<!DOCTYPE html>
<html lang="zh-CN">
<head>
  <meta charset="UTF-8">
<meta name="viewport" content="width=device-width">
<meta name="theme-color" content="#222" media="(prefers-color-scheme: light)">
<meta name="theme-color" content="#222" media="(prefers-color-scheme: dark)"><meta name="generator" content="Hexo 5.2.0">


  <link rel="apple-touch-icon" sizes="180x180" href="/images/apple-touch-icon-next.png">
  <link rel="icon" type="image/png" sizes="32x32" href="/images/favicon-32x32-next.png">
  <link rel="icon" type="image/png" sizes="16x16" href="/images/favicon-16x16-next.png">
  <link rel="mask-icon" href="/images/logo.svg" color="#222">

<link rel="stylesheet" href="/css/main.css">



<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.1.2/css/all.min.css" integrity="sha256-xejo6yLi6vGtAjcMIsY8BHdKsLg7QynVlFMzdQgUuy8=" crossorigin="anonymous">
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/animate.css/3.1.1/animate.min.css" integrity="sha256-PR7ttpcvz8qrF57fur/yAx1qXMFJeJFiA6pSzWi0OIE=" crossorigin="anonymous">
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/fancybox/3.5.7/jquery.fancybox.min.css" integrity="sha256-Vzbj7sDDS/woiFS3uNKo8eIuni59rjyNGtXfstRzStA=" crossorigin="anonymous">

<script class="next-config" data-name="main" type="application/json">{"hostname":"gz1234.gitee.io","root":"/","images":"/images","scheme":"Gemini","darkmode":true,"version":"8.12.3","exturl":false,"sidebar":{"position":"left","display":"post","padding":18,"offset":12},"copycode":{"enable":true,"style":"flat"},"bookmark":{"enable":true,"color":"#222","save":"auto"},"mediumzoom":false,"lazyload":false,"pangu":false,"comments":{"style":"tabs","active":null,"storage":true,"lazyload":false,"nav":null},"stickytabs":false,"motion":{"enable":true,"async":false,"transition":{"post_block":"fadeIn","post_header":"fadeInDown","post_body":"fadeInDown","coll_header":"fadeInLeft","sidebar":"fadeInUp"}},"prism":false,"i18n":{"placeholder":"搜索...","empty":"没有找到任何搜索结果：${query}","hits_time":"找到 ${hits} 个搜索结果（用时 ${time} 毫秒）","hits":"找到 ${hits} 个搜索结果"},"path":"/search.xml","localsearch":{"enable":true,"trigger":"auto","top_n_per_article":1,"unescape":false,"preload":false}}</script><script src="/js/config.js"></script>

    <meta property="og:type" content="website">
<meta property="og:title" content="郭泽">
<meta property="og:url" content="https://gz1234.gitee.io/page/2/index.html">
<meta property="og:site_name" content="郭泽">
<meta property="og:locale" content="zh_CN">
<meta property="article:author" content="郭泽">
<meta name="twitter:card" content="summary">


<link rel="canonical" href="https://gz1234.gitee.io/page/2/">



<script class="next-config" data-name="page" type="application/json">{"sidebar":"","isHome":true,"isPost":false,"lang":"zh-CN","comments":"","permalink":"","path":"page/2/index.html","title":""}</script>

<script class="next-config" data-name="calendar" type="application/json">""</script>
<title>郭泽</title>
  





  <noscript>
    <link rel="stylesheet" href="/css/noscript.css">
  </noscript>
</head>

<body itemscope itemtype="http://schema.org/WebPage" class="use-motion">
  <div class="headband"></div>

  <main class="main">
    <header class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-brand-container">
  <div class="site-nav-toggle">
    <div class="toggle" aria-label="切换导航栏" role="button">
        <span class="toggle-line"></span>
        <span class="toggle-line"></span>
        <span class="toggle-line"></span>
    </div>
  </div>

  <div class="site-meta">

    <a href="/" class="brand" rel="start">
      <i class="logo-line"></i>
      <h1 class="site-title">郭泽</h1>
      <i class="logo-line"></i>
    </a>
  </div>

  <div class="site-nav-right">
    <div class="toggle popup-trigger">
        <i class="fa fa-search fa-fw fa-lg"></i>
    </div>
  </div>
</div>



<nav class="site-nav">
  <ul class="main-menu menu"><li class="menu-item menu-item-home"><a href="/" rel="section"><i class="fa fa-home fa-fw"></i>首页</a></li><li class="menu-item menu-item-about"><a href="/about/" rel="section"><i class="fa fa-user fa-fw"></i>关于</a></li><li class="menu-item menu-item-tags"><a href="/tags/" rel="section"><i class="fa fa-tags fa-fw"></i>标签</a></li><li class="menu-item menu-item-categories"><a href="/categories/" rel="section"><i class="fa fa-th fa-fw"></i>分类</a></li><li class="menu-item menu-item-archives"><a href="/archives/" rel="section"><i class="fa fa-archive fa-fw"></i>归档</a></li>
      <li class="menu-item menu-item-search">
        <a role="button" class="popup-trigger"><i class="fa fa-search fa-fw"></i>搜索
        </a>
      </li>
  </ul>
</nav>



  <div class="search-pop-overlay">
    <div class="popup search-popup"><div class="search-header">
  <span class="search-icon">
    <i class="fa fa-search"></i>
  </span>
  <div class="search-input-container">
    <input autocomplete="off" autocapitalize="off" maxlength="80"
           placeholder="搜索..." spellcheck="false"
           type="search" class="search-input">
  </div>
  <span class="popup-btn-close" role="button">
    <i class="fa fa-times-circle"></i>
  </span>
</div>
<div class="search-result-container no-result">
  <div class="search-result-icon">
    <i class="fa fa-spinner fa-pulse fa-5x"></i>
  </div>
</div>

    </div>
  </div>

</div>
        
  
  <div class="toggle sidebar-toggle" role="button">
    <span class="toggle-line"></span>
    <span class="toggle-line"></span>
    <span class="toggle-line"></span>
  </div>

  <aside class="sidebar">

    <div class="sidebar-inner sidebar-overview-active">
      <ul class="sidebar-nav">
        <li class="sidebar-nav-toc">
          文章目录
        </li>
        <li class="sidebar-nav-overview">
          站点概览
        </li>
      </ul>

      <div class="sidebar-panel-container">
        <!--noindex-->
        <div class="post-toc-wrap sidebar-panel">
        </div>
        <!--/noindex-->

        <div class="site-overview-wrap sidebar-panel">
          <div class="site-author site-overview-item animated" itemprop="author" itemscope itemtype="http://schema.org/Person">
  <p class="site-author-name" itemprop="name">郭泽</p>
  <div class="site-description" itemprop="description"></div>
</div>
<div class="site-state-wrap site-overview-item animated">
  <nav class="site-state">
      <div class="site-state-item site-state-posts">
        <a href="/archives/">
          <span class="site-state-item-count">54</span>
          <span class="site-state-item-name">日志</span>
        </a>
      </div>
      <div class="site-state-item site-state-categories">
          <a href="/categories/">
        <span class="site-state-item-count">21</span>
        <span class="site-state-item-name">分类</span></a>
      </div>
      <div class="site-state-item site-state-tags">
          <a href="/tags/">
        <span class="site-state-item-count">51</span>
        <span class="site-state-item-name">标签</span></a>
      </div>
  </nav>
</div>



        </div>
      </div>
    </div>
  </aside>
  <div class="sidebar-dimmer"></div>


    </header>

    
  <div class="back-to-top" role="button" aria-label="返回顶部">
    <i class="fa fa-arrow-up"></i>
    <span>0%</span>
  </div>
  <a role="button" class="book-mark-link book-mark-link-fixed"></a>

<noscript>
  <div class="noscript-warning">Theme NexT works best with JavaScript enabled</div>
</noscript>


    <div class="main-inner index posts-expand">

    


<div class="post-block">
  
  

  <article itemscope itemtype="http://schema.org/Article" class="post-content" lang="">
    <link itemprop="mainEntityOfPage" href="https://gz1234.gitee.io/2022/07/27/vscode%E5%BF%AB%E6%8D%B7%E9%94%AE/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="image" content="/images/avatar.gif">
      <meta itemprop="name" content="郭泽">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="郭泽">
      <meta itemprop="description" content="">
    </span>

    <span hidden itemprop="post" itemscope itemtype="http://schema.org/CreativeWork">
      <meta itemprop="name" content="undefined | 郭泽">
      <meta itemprop="description" content="">
    </span>
      <header class="post-header">
        <h2 class="post-title" itemprop="name headline">
          <a href="/2022/07/27/vscode%E5%BF%AB%E6%8D%B7%E9%94%AE/" class="post-title-link" itemprop="url">vscode快捷键</a>
        </h2>

        <div class="post-meta-container">
          <div class="post-meta">
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-calendar"></i>
      </span>
      <span class="post-meta-item-text">发表于</span>

      <time title="创建时间：2022-07-27 15:47:52" itemprop="dateCreated datePublished" datetime="2022-07-27T15:47:52+08:00">2022-07-27</time>
    </span>
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-calendar-check"></i>
      </span>
      <span class="post-meta-item-text">更新于</span>
      <time title="修改时间：2022-08-02 11:24:55" itemprop="dateModified" datetime="2022-08-02T11:24:55+08:00">2022-08-02</time>
    </span>
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-folder"></i>
      </span>
      <span class="post-meta-item-text">分类于</span>
        <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
          <a href="/categories/vscode%E5%BF%AB%E6%8D%B7%E9%94%AE/" itemprop="url" rel="index"><span itemprop="name">vscode快捷键</span></a>
        </span>
    </span>

  
</div>

        </div>
      </header>

    
    
    
    <div class="post-body" itemprop="articleBody">
          <h2 id="编辑器与窗口管理"><a href="#编辑器与窗口管理" class="headerlink" title="编辑器与窗口管理"></a>编辑器与窗口管理</h2><ul>
<li>Ctrl+N： 新建文件</li>
<li>Ctrl+Shift+N：打开一个新的VSCode编辑器</li>
<li>Ctrl+O：打开文件</li>
<li>Ctrl+Shift+O：打开文件夹</li>
<li>Ctrl+W：关闭当前文件</li>
<li>Ctrl+Shift+W 或 Alt+F4：关闭当前VSCode编辑器</li>
<li>Ctrl+\：新建窗口显示代码（相当于复制当前代码到一个新的窗口；同一引用，修改一个文件，其他相同文件会一起改变）</li>
<li>Ctrl+Alt+→：移动当前文件到右窗口，若没有右窗口，则创建一个新窗口</li>
<li>Ctrl+Alt+←：移动当前文件到左窗口</li>
<li>Ctrl+Tab：切换文件窗口</li>
<li>Ctrl+B：显示/隐藏侧边栏</li>
<li>Ctrl+`：显示/隐藏控制面板（Terminal）（反引号位置：英文输入法状态下，键盘ESC按键下面的按键）</li>
<li>Ctrl +/- ：放大/缩小编辑器窗口</li>
<li>F11：全屏显示</li>
<li>Ctrl+Shift+E：文件资源管理器（Explorer）</li>
<li>Ctrl+Shift+G：git管理窗口（Source Control）</li>
<li>Ctrl+Shift+X：扩展（插件）管理窗口（Extentions）</li>
</ul>
          <!--noindex-->
            <div class="post-button">
              <a class="btn" href="/2022/07/27/vscode%E5%BF%AB%E6%8D%B7%E9%94%AE/#more" rel="contents">
                阅读全文 &raquo;
              </a>
            </div>
          <!--/noindex-->
        
      
    </div>

    
    
    

    <footer class="post-footer">
        <div class="post-eof"></div>
      
    </footer>
  </article>
</div>




    


<div class="post-block">
  
  

  <article itemscope itemtype="http://schema.org/Article" class="post-content" lang="">
    <link itemprop="mainEntityOfPage" href="https://gz1234.gitee.io/2022/07/27/%E7%AE%97%E6%B3%95%E6%80%9D%E6%83%B3/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="image" content="/images/avatar.gif">
      <meta itemprop="name" content="郭泽">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="郭泽">
      <meta itemprop="description" content="">
    </span>

    <span hidden itemprop="post" itemscope itemtype="http://schema.org/CreativeWork">
      <meta itemprop="name" content="undefined | 郭泽">
      <meta itemprop="description" content="">
    </span>
      <header class="post-header">
        <h2 class="post-title" itemprop="name headline">
          <a href="/2022/07/27/%E7%AE%97%E6%B3%95%E6%80%9D%E6%83%B3/" class="post-title-link" itemprop="url">算法思想</a>
        </h2>

        <div class="post-meta-container">
          <div class="post-meta">
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-calendar"></i>
      </span>
      <span class="post-meta-item-text">发表于</span>

      <time title="创建时间：2022-07-27 10:00:55" itemprop="dateCreated datePublished" datetime="2022-07-27T10:00:55+08:00">2022-07-27</time>
    </span>
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-calendar-check"></i>
      </span>
      <span class="post-meta-item-text">更新于</span>
      <time title="修改时间：2022-11-22 11:28:43" itemprop="dateModified" datetime="2022-11-22T11:28:43+08:00">2022-11-22</time>
    </span>
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-folder"></i>
      </span>
      <span class="post-meta-item-text">分类于</span>
        <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
          <a href="/categories/%E7%AE%97%E6%B3%95%E6%80%9D%E6%83%B3/" itemprop="url" rel="index"><span itemprop="name">算法思想</span></a>
        </span>
    </span>

  
</div>

        </div>
      </header>

    
    
    
    <div class="post-body" itemprop="articleBody">
          <h2 id="算法思想"><a href="#算法思想" class="headerlink" title="算法思想"></a>算法思想</h2><ul>
<li><p>二分</p>
</li>
<li><p>双指针 （快慢指针 头尾指针） 1.链表 数组</p>
</li>
<li><p>递归 回溯 （画递归树）</p>
<figure class="highlight javascript"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="function"><span class="keyword">function</span> <span class="title">backTrack</span>(<span class="params">数据， 路径缓存</span>)</span>&#123;</span><br><span class="line"> <span class="keyword">for</span> (下一个值)</span><br><span class="line"> 标记</span><br><span class="line"> backTrack</span><br><span class="line"> 取消标记</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>
</li>
<li><p>动态规划 找最优解</p>
</li>
<li><p>贪心</p>
</li>
<li><p>bfs(宽度优先) dfs(回溯 广度优先)</p>
</li>
</ul>
          <!--noindex-->
            <div class="post-button">
              <a class="btn" href="/2022/07/27/%E7%AE%97%E6%B3%95%E6%80%9D%E6%83%B3/#more" rel="contents">
                阅读全文 &raquo;
              </a>
            </div>
          <!--/noindex-->
        
      
    </div>

    
    
    

    <footer class="post-footer">
        <div class="post-eof"></div>
      
    </footer>
  </article>
</div>




    


<div class="post-block">
  
  

  <article itemscope itemtype="http://schema.org/Article" class="post-content" lang="">
    <link itemprop="mainEntityOfPage" href="https://gz1234.gitee.io/2022/07/10/%E4%BB%A3%E7%A0%81%E4%B8%AD%E6%B3%A8%E6%84%8F%E9%97%AE%E9%A2%98/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="image" content="/images/avatar.gif">
      <meta itemprop="name" content="郭泽">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="郭泽">
      <meta itemprop="description" content="">
    </span>

    <span hidden itemprop="post" itemscope itemtype="http://schema.org/CreativeWork">
      <meta itemprop="name" content="undefined | 郭泽">
      <meta itemprop="description" content="">
    </span>
      <header class="post-header">
        <h2 class="post-title" itemprop="name headline">
          <a href="/2022/07/10/%E4%BB%A3%E7%A0%81%E4%B8%AD%E6%B3%A8%E6%84%8F%E9%97%AE%E9%A2%98/" class="post-title-link" itemprop="url">代码中注意问题</a>
        </h2>

        <div class="post-meta-container">
          <div class="post-meta">
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-calendar"></i>
      </span>
      <span class="post-meta-item-text">发表于</span>

      <time title="创建时间：2022-07-10 00:55:43" itemprop="dateCreated datePublished" datetime="2022-07-10T00:55:43+08:00">2022-07-10</time>
    </span>
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-calendar-check"></i>
      </span>
      <span class="post-meta-item-text">更新于</span>
      <time title="修改时间：2023-08-11 17:52:59" itemprop="dateModified" datetime="2023-08-11T17:52:59+08:00">2023-08-11</time>
    </span>
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-folder"></i>
      </span>
      <span class="post-meta-item-text">分类于</span>
        <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
          <a href="/categories/%E4%BB%A3%E7%A0%81%E4%B8%AD%E6%B3%A8%E6%84%8F%E9%97%AE%E9%A2%98/" itemprop="url" rel="index"><span itemprop="name">代码中注意问题</span></a>
        </span>
    </span>

  
</div>

        </div>
      </header>

    
    
    
    <div class="post-body" itemprop="articleBody">
          <h2 id="Object-keys-的顺序问题"><a href="#Object-keys-的顺序问题" class="headerlink" title="Object.keys()的顺序问题"></a>Object.keys()的顺序问题</h2><ul>
<li><p>将所有「合法的数组索引」按升序排序</p>
</li>
<li><p>将所有「字符串类型索引」按属性创建时间以升序排序</p>
</li>
<li><p>将所有 「Symbol 类型索引」按属性创建时间以升序排序</p>
</li>
<li><p>「<code>合法数组索引指的是正整数，负数或者浮点数一律当做字符串处理。严格来说对象属性没有数字类型的，无论是数字还是字符串，都会被当做字符串来处理。</code>」</p>
</li>
</ul>
<p><a target="_blank" rel="noopener" href="https://blog.csdn.net/qq846294282/article/details/118719889">Object.keys</a><br><a target="_blank" rel="noopener" href="https://mp.weixin.qq.com/s/2_yFU6iS9ALOXgM--LyaEA">Object.keys</a></p>
<h2 id="JSON-stringify-使用数据丢失问题"><a href="#JSON-stringify-使用数据丢失问题" class="headerlink" title="JSON.stringify 使用数据丢失问题"></a>JSON.stringify 使用数据丢失问题</h2><p><a target="_blank" rel="noopener" href="https://mp.weixin.qq.com/s/rTuiUtpTB6iOqCNb7yv0zQ">JSON.stringify</a></p>
<h2 id="if-else-语句里不能用函数声明定义函数"><a href="#if-else-语句里不能用函数声明定义函数" class="headerlink" title="if else 语句里不能用函数声明定义函数"></a>if else 语句里不能用函数声明定义函数</h2><h2 id="js-toFixed四舍五入出现的精度问题"><a href="#js-toFixed四舍五入出现的精度问题" class="headerlink" title="js toFixed四舍五入出现的精度问题"></a>js toFixed四舍五入出现的精度问题</h2><p><a target="_blank" rel="noopener" href="https://blog.csdn.net/qq_38128179/article/details/89181805?spm=1001.2101.3001.6650.1&utm_medium=distribute.pc_relevant.none-task-blog-2~default~CTRLIST~default-1-89181805-blog-123878883.pc_relevant_aa&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2~default~CTRLIST~default-1-89181805-blog-123878883.pc_relevant_aa&utm_relevant_index=2">解决JS浮点数运算结果不精确的Bug</a><br> 解决办法的类库 <a target="_blank" rel="noopener" href="https://mathjs.org/">Math.js</a> <a target="_blank" rel="noopener" href="http://mikemcl.github.io/big.js/">big.js</a> <a target="_blank" rel="noopener" href="http://mikemcl.github.io/decimal.js/">decimal.js</a></p>
<h2 id="forEach中不出现异步代码"><a href="#forEach中不出现异步代码" class="headerlink" title="forEach中不出现异步代码"></a>forEach中不出现异步代码</h2>
          <!--noindex-->
            <div class="post-button">
              <a class="btn" href="/2022/07/10/%E4%BB%A3%E7%A0%81%E4%B8%AD%E6%B3%A8%E6%84%8F%E9%97%AE%E9%A2%98/#more" rel="contents">
                阅读全文 &raquo;
              </a>
            </div>
          <!--/noindex-->
        
      
    </div>

    
    
    

    <footer class="post-footer">
        <div class="post-eof"></div>
      
    </footer>
  </article>
</div>




    


<div class="post-block">
  
  

  <article itemscope itemtype="http://schema.org/Article" class="post-content" lang="">
    <link itemprop="mainEntityOfPage" href="https://gz1234.gitee.io/2022/07/09/%E5%89%8D%E7%AB%AF%E8%AE%B0%E5%BD%95%E6%95%B4%E7%90%86/Buffer/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="image" content="/images/avatar.gif">
      <meta itemprop="name" content="郭泽">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="郭泽">
      <meta itemprop="description" content="">
    </span>

    <span hidden itemprop="post" itemscope itemtype="http://schema.org/CreativeWork">
      <meta itemprop="name" content="undefined | 郭泽">
      <meta itemprop="description" content="">
    </span>
      <header class="post-header">
        <h2 class="post-title" itemprop="name headline">
          <a href="/2022/07/09/%E5%89%8D%E7%AB%AF%E8%AE%B0%E5%BD%95%E6%95%B4%E7%90%86/Buffer/" class="post-title-link" itemprop="url">Buffer</a>
        </h2>

        <div class="post-meta-container">
          <div class="post-meta">
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-calendar"></i>
      </span>
      <span class="post-meta-item-text">发表于</span>

      <time title="创建时间：2022-07-09 03:21:52" itemprop="dateCreated datePublished" datetime="2022-07-09T03:21:52+08:00">2022-07-09</time>
    </span>
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-calendar-check"></i>
      </span>
      <span class="post-meta-item-text">更新于</span>
      <time title="修改时间：2022-11-16 10:09:11" itemprop="dateModified" datetime="2022-11-16T10:09:11+08:00">2022-11-16</time>
    </span>
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-folder"></i>
      </span>
      <span class="post-meta-item-text">分类于</span>
        <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
          <a href="/categories/%E5%89%8D%E7%AB%AF%E6%95%B4%E7%90%86/" itemprop="url" rel="index"><span itemprop="name">前端整理</span></a>
        </span>
    </span>

  
</div>

        </div>
      </header>

    
    
    
    <div class="post-body" itemprop="articleBody">
          <h2 id="什么是Buffer"><a href="#什么是Buffer" class="headerlink" title="什么是Buffer"></a>什么是Buffer</h2><ul>
<li>缓冲区Buffer是暂时存放输入输出数据的一段内存。</li>
<li>JS语言没有二进制数据类型，而在处理TCP和文件流的时候，必须要处理二进制数据。</li>
<li>NodeJS提供了一个Buffer对象来提供对二进制数据的操作</li>
<li>是一个表示固定内存分配的全局对象，也就是说要放到缓存区中的字节数需要提前确定</li>
<li>Buffer好比由一个8位字节元素组成的数组，可以有效的在JavasScript中表示二进制数据</li>
</ul>
<h2 id="什么是字节"><a href="#什么是字节" class="headerlink" title="什么是字节"></a>什么是字节</h2><ul>
<li>字节(Byte)是计算机存储时的一种计量单位，一个字节等于8位二进制数</li>
<li>一个位就代表一个0或1，每8个位（bit）组成一个字节（Byte）</li>
<li>字节是通过网络传输信息的基本单位</li>
<li>一个字节最大值十进制数是255(2**8-1)</li>
</ul>
<h2 id="进制"><a href="#进制" class="headerlink" title="进制"></a>进制</h2><ul>
<li><p>0b 2进制</p>
</li>
<li><p>0x 16进制</p>
</li>
<li><p>0o 8进制</p>
</li>
<li><p>将任意进制字符串转换为十进制</p></li></ul>
          <!--noindex-->
            <div class="post-button">
              <a class="btn" href="/2022/07/09/%E5%89%8D%E7%AB%AF%E8%AE%B0%E5%BD%95%E6%95%B4%E7%90%86/Buffer/#more" rel="contents">
                阅读全文 &raquo;
              </a>
            </div>
          <!--/noindex-->
        
      
    </div>

    
    
    

    <footer class="post-footer">
        <div class="post-eof"></div>
      
    </footer>
  </article>
</div>




    


<div class="post-block">
  
  

  <article itemscope itemtype="http://schema.org/Article" class="post-content" lang="">
    <link itemprop="mainEntityOfPage" href="https://gz1234.gitee.io/2022/07/09/%E5%89%8D%E7%AB%AF%E8%AE%B0%E5%BD%95%E6%95%B4%E7%90%86/Encoding/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="image" content="/images/avatar.gif">
      <meta itemprop="name" content="郭泽">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="郭泽">
      <meta itemprop="description" content="">
    </span>

    <span hidden itemprop="post" itemscope itemtype="http://schema.org/CreativeWork">
      <meta itemprop="name" content="undefined | 郭泽">
      <meta itemprop="description" content="">
    </span>
      <header class="post-header">
        <h2 class="post-title" itemprop="name headline">
          <a href="/2022/07/09/%E5%89%8D%E7%AB%AF%E8%AE%B0%E5%BD%95%E6%95%B4%E7%90%86/Encoding/" class="post-title-link" itemprop="url">Encoding</a>
        </h2>

        <div class="post-meta-container">
          <div class="post-meta">
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-calendar"></i>
      </span>
      <span class="post-meta-item-text">发表于</span>

      <time title="创建时间：2022-07-09 03:21:52" itemprop="dateCreated datePublished" datetime="2022-07-09T03:21:52+08:00">2022-07-09</time>
    </span>
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-calendar-check"></i>
      </span>
      <span class="post-meta-item-text">更新于</span>
      <time title="修改时间：2022-11-16 10:09:11" itemprop="dateModified" datetime="2022-11-16T10:09:11+08:00">2022-11-16</time>
    </span>
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-folder"></i>
      </span>
      <span class="post-meta-item-text">分类于</span>
        <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
          <a href="/categories/%E5%89%8D%E7%AB%AF%E6%95%B4%E7%90%86/" itemprop="url" rel="index"><span itemprop="name">前端整理</span></a>
        </span>
    </span>

  
</div>

        </div>
      </header>

    
    
    
    <div class="post-body" itemprop="articleBody">
          <h2 id="字符发展历史"><a href="#字符发展历史" class="headerlink" title="字符发展历史 #"></a>字符发展历史 <a href="#t01-%E5%AD%97%E7%AC%A6%E5%8F%91%E5%B1%95%E5%8E%86%E5%8F%B2">#</a></h2><h3 id="字节"><a href="#字节" class="headerlink" title="字节 #"></a>字节 <a href="#t111-%E5%AD%97%E8%8A%82">#</a></h3><ul>
<li>计算机内部，所有信息最终都是一个二进制值</li>
<li>每一个二进制位（bit）有0和1两种状态，因此八个二进制位就可以组合出256种状态，这被称为一个字节(byte)<br><img src="/2022/07/09/%E5%89%8D%E7%AB%AF%E8%AE%B0%E5%BD%95%E6%95%B4%E7%90%86/Encoding/bits.jpg"></li>
</ul>
<h3 id="单位"><a href="#单位" class="headerlink" title="单位 #"></a>单位 <a href="#t212-%E5%8D%95%E4%BD%8D">#</a></h3><ul>
<li>8位 = 1字节</li>
<li>1024字节 = 1K</li>
<li>1024K = 1M</li>
<li>1024M = 1G</li>
<li>1024G = 1T</li></ul>
          <!--noindex-->
            <div class="post-button">
              <a class="btn" href="/2022/07/09/%E5%89%8D%E7%AB%AF%E8%AE%B0%E5%BD%95%E6%95%B4%E7%90%86/Encoding/#more" rel="contents">
                阅读全文 &raquo;
              </a>
            </div>
          <!--/noindex-->
        
      
    </div>

    
    
    

    <footer class="post-footer">
        <div class="post-eof"></div>
      
    </footer>
  </article>
</div>




    


<div class="post-block">
  
  

  <article itemscope itemtype="http://schema.org/Article" class="post-content" lang="">
    <link itemprop="mainEntityOfPage" href="https://gz1234.gitee.io/2022/07/09/%E5%89%8D%E7%AB%AF%E8%AE%B0%E5%BD%95%E6%95%B4%E7%90%86/%E5%8D%95%E5%85%83%E6%B5%8B%E8%AF%95/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="image" content="/images/avatar.gif">
      <meta itemprop="name" content="郭泽">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="郭泽">
      <meta itemprop="description" content="">
    </span>

    <span hidden itemprop="post" itemscope itemtype="http://schema.org/CreativeWork">
      <meta itemprop="name" content="undefined | 郭泽">
      <meta itemprop="description" content="">
    </span>
      <header class="post-header">
        <h2 class="post-title" itemprop="name headline">
          <a href="/2022/07/09/%E5%89%8D%E7%AB%AF%E8%AE%B0%E5%BD%95%E6%95%B4%E7%90%86/%E5%8D%95%E5%85%83%E6%B5%8B%E8%AF%95/" class="post-title-link" itemprop="url">单元测试</a>
        </h2>

        <div class="post-meta-container">
          <div class="post-meta">
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-calendar"></i>
      </span>
      <span class="post-meta-item-text">发表于</span>

      <time title="创建时间：2022-07-09 03:21:52" itemprop="dateCreated datePublished" datetime="2022-07-09T03:21:52+08:00">2022-07-09</time>
    </span>
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-calendar-check"></i>
      </span>
      <span class="post-meta-item-text">更新于</span>
      <time title="修改时间：2023-04-03 09:42:00" itemprop="dateModified" datetime="2023-04-03T09:42:00+08:00">2023-04-03</time>
    </span>
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-folder"></i>
      </span>
      <span class="post-meta-item-text">分类于</span>
        <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
          <a href="/categories/%E5%89%8D%E7%AB%AF%E6%95%B4%E7%90%86/" itemprop="url" rel="index"><span itemprop="name">前端整理</span></a>
        </span>
    </span>

  
</div>

        </div>
      </header>

    
    
    
    <div class="post-body" itemprop="articleBody">
          <h2 id="Jest"><a href="#Jest" class="headerlink" title="Jest"></a>Jest</h2><p>Facebook出品, 老牌单元测试工具</p>
<p><a target="_blank" rel="noopener" href="https://jestjs.io/zh-Hans/docs/getting-started">文档地址</a></p>
<h3 id="生命周期"><a href="#生命周期" class="headerlink" title="生命周期"></a>生命周期</h3><figure class="highlight js"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">test			<span class="comment">// 测试用例</span></span><br><span class="line">it				<span class="comment">// test别名, 功能相同</span></span><br><span class="line">describe		<span class="comment">// 测试分组</span></span><br><span class="line">beforeAll		<span class="comment">// 应用到每次测试中, 全局测试开始前</span></span><br><span class="line">afterAll		<span class="comment">// 应用到每次测试中, 全局测试结束后</span></span><br><span class="line">beforeEach		<span class="comment">// 应用到当前测试中, 当前测试开始后</span></span><br><span class="line">afterEach		<span class="comment">// 应用到当前测试中, 当前测试结束后</span></span><br></pre></td></tr></table></figure>

<figure class="highlight js"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br></pre></td><td class="code"><pre><span class="line">beforeAll(<span class="function">() =&gt;</span> <span class="built_in">console</span>.log(<span class="string">&#x27;1 - beforeAll&#x27;</span>));</span><br><span class="line">afterAll(<span class="function">() =&gt;</span> <span class="built_in">console</span>.log(<span class="string">&#x27;1 - afterAll&#x27;</span>));</span><br><span class="line">beforeEach(<span class="function">() =&gt;</span> <span class="built_in">console</span>.log(<span class="string">&#x27;1 - beforeEach&#x27;</span>));</span><br><span class="line">afterEach(<span class="function">() =&gt;</span> <span class="built_in">console</span>.log(<span class="string">&#x27;1 - afterEach&#x27;</span>));</span><br><span class="line"></span><br><span class="line">test(<span class="string">&#x27;&#x27;</span>, <span class="function">() =&gt;</span> <span class="built_in">console</span>.log(<span class="string">&#x27;1 - test&#x27;</span>));</span><br><span class="line"></span><br><span class="line">describe(<span class="string">&#x27;Scoped / Nested block&#x27;</span>, <span class="function">() =&gt;</span> &#123;</span><br><span class="line">  beforeAll(<span class="function">() =&gt;</span> <span class="built_in">console</span>.log(<span class="string">&#x27;2 - beforeAll&#x27;</span>));</span><br><span class="line">  afterAll(<span class="function">() =&gt;</span> <span class="built_in">console</span>.log(<span class="string">&#x27;2 - afterAll&#x27;</span>));</span><br><span class="line">  beforeEach(<span class="function">() =&gt;</span> <span class="built_in">console</span>.log(<span class="string">&#x27;2 - beforeEach&#x27;</span>));</span><br><span class="line">  afterEach(<span class="function">() =&gt;</span> <span class="built_in">console</span>.log(<span class="string">&#x27;2 - afterEach&#x27;</span>));</span><br><span class="line"></span><br><span class="line">  test(<span class="string">&#x27;&#x27;</span>, <span class="function">() =&gt;</span> <span class="built_in">console</span>.log(<span class="string">&#x27;2 - test&#x27;</span>));</span><br><span class="line">&#125;);</span><br><span class="line"></span><br><span class="line"><span class="comment">// 1 - beforeAll</span></span><br><span class="line"><span class="comment">// 1 - beforeEach</span></span><br><span class="line"><span class="comment">// 1 - test</span></span><br><span class="line"><span class="comment">// 1 - afterEach</span></span><br><span class="line"><span class="comment">// 2 - beforeAll</span></span><br><span class="line"><span class="comment">// 1 - beforeEach</span></span><br><span class="line"><span class="comment">// 2 - beforeEach</span></span><br><span class="line"><span class="comment">// 2 - test</span></span><br><span class="line"><span class="comment">// 2 - afterEach</span></span><br><span class="line"><span class="comment">// 1 - afterEach</span></span><br><span class="line"><span class="comment">// 2 - afterAll</span></span><br><span class="line"><span class="comment">// 1 - afterAll</span></span><br></pre></td></tr></table></figure>



<h3 id="Expect断言"><a href="#Expect断言" class="headerlink" title="Expect断言"></a>Expect断言</h3><p>在写测试的时候，我们经常需要检查值是否满足指定的条件。 <code>expect</code> 让你可以使用不同的“匹配器”去验证不同类型的东西。</p>
<figure class="highlight js"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line">expect(value)							<span class="comment">// 判断一个值是否满足条件, 搭配匹配器使用</span></span><br><span class="line">expect.extend(matchers)					<span class="comment">// 添加自定义匹配器, 传入对象可设置多个匹配器方法</span></span><br><span class="line">expect.anything()						<span class="comment">// 匹配是否是null和undefined之外的任何内容</span></span><br><span class="line">expect.any(<span class="title">constructor</span>)					// 匹配构造函数创建的实例, 或者原始值类型</span><br><span class="line"><span class="title">expect</span>.<span class="title">arrayContaining</span>(<span class="params">array</span>)			// 匹配接收到的数组, 预期数组是接收数组的子集</span><br><span class="line"><span class="title">expect</span>.<span class="title">assertions</span>(<span class="params">number</span>) 				// 验证在测试期间是否调用了一定数量的断言</span><br><span class="line"><span class="title">expect</span>.<span class="title">hasAssertions</span>(<span class="params"></span>)					// 验证在测试期间至少调用了一个断言</span><br><span class="line"><span class="title">expect</span>.<span class="title">closeTo</span>(<span class="params">number, numDigits?</span>)		// 验证浮点数的计算结果, 第二个参数为限制精确位数</span><br><span class="line"><span class="title">expect</span>.<span class="title">not</span>.<span class="title">arrayContaining</span>(<span class="params">array</span>)		// 匹配接收到的数组，该数组不包含预期数组中的所有元素</span><br><span class="line"><span class="title">expect</span>.<span class="title">not</span>.<span class="title">objectContaining</span>(<span class="params">object</span>)		// 匹配接收到的对象, 预期对象不是接收对象的子集</span><br><span class="line"><span class="title">expect</span>.<span class="title">not</span>.<span class="title">stringContaining</span>(<span class="params">string</span>)		// 验证不是一个字符串, 或不包含预期字符串的字符串</span><br><span class="line"><span class="title">expect</span>.<span class="title">objectContaining</span>(<span class="params">object</span>)			// 验证预期对象是接收对象的子集</span><br><span class="line"><span class="title">expect</span>.<span class="title">stringContaining</span>(<span class="params">string</span>)			// 验证预期字符串是接收字符串所包含的</span><br><span class="line"><span class="title">expect</span>.<span class="title">stringMatching</span>(<span class="params">string | regexp</span>)	// 验证是与预期字符串或正则表达式匹配的字符串</span><br></pre></td></tr></table></figure>



<h3 id="匹配器"><a href="#匹配器" class="headerlink" title="匹配器"></a>匹配器</h3><figure class="highlight js"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">.toBe(value)					<span class="comment">// 匹配原始值, 或对象的属性值</span></span><br><span class="line">.toBeCloseTo					<span class="comment">// 匹配浮点数</span></span><br><span class="line">.toBeDefined					<span class="comment">// 检查变量是否未定义</span></span><br><span class="line">.toHaveLength(number)			<span class="comment">// 检查对象是否有length属性, 或指定length值</span></span><br><span class="line">.toBeNull()						<span class="comment">// 检查是否为空</span></span><br><span class="line">.toBeUndefined()				<span class="comment">// 检查变量是否未定义</span></span><br><span class="line">.toBeNaN()						<span class="comment">// 检查值为NaN</span></span><br><span class="line">.toEqual(value)					<span class="comment">// 比较对象是否相等</span></span><br><span class="line">.toMatch(regexp | string)		<span class="comment">// 检查字符串是否与正则表达式匹配</span></span><br><span class="line">.toThrow(error?)				<span class="comment">// 检查是否为抛出异常</span></span><br></pre></td></tr></table></figure>



<h3 id="DOM仿真"><a href="#DOM仿真" class="headerlink" title="DOM仿真"></a>DOM仿真</h3><figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">pnpm i jsdom -D</span><br></pre></td></tr></table></figure>

<figure class="highlight js"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// jsdom-config.js</span></span><br><span class="line"><span class="keyword">const</span> jsdom = <span class="built_in">require</span>(<span class="string">&#x27;jsdom&#x27;</span>) <span class="comment">// eslint-disable-line</span></span><br><span class="line"><span class="keyword">const</span> &#123; JSDOM &#125; = jsdom</span><br><span class="line"></span><br><span class="line"><span class="keyword">const</span> dom = <span class="keyword">new</span> JSDOM(<span class="string">&#x27;&lt;!DOCTYPE html&gt;&lt;head/&gt;&lt;body&gt;&lt;/body&gt;&#x27;</span>, &#123;</span><br><span class="line">  url: <span class="string">&#x27;http://localhost/&#x27;</span>,</span><br><span class="line">  referrer: <span class="string">&#x27;https://example.com/&#x27;</span>,</span><br><span class="line">  contentType: <span class="string">&#x27;text/html&#x27;</span>,</span><br><span class="line">  userAgent: <span class="string">&#x27;Mellblomenator/9000&#x27;</span>,</span><br><span class="line">  includeNodeLocations: <span class="literal">true</span>,</span><br><span class="line">  storageQuota: <span class="number">10000000</span>,</span><br><span class="line">&#125;)</span><br><span class="line"><span class="built_in">global</span>.window = dom.window</span><br><span class="line"><span class="built_in">global</span>.document = <span class="built_in">window</span>.document</span><br><span class="line"><span class="built_in">global</span>.navigator = <span class="built_in">window</span>.navigator</span><br></pre></td></tr></table></figure>

<figure class="highlight js"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// dom.js</span></span><br><span class="line"><span class="built_in">exports</span>.generateDiv = <span class="function">() =&gt;</span> &#123;</span><br><span class="line">  <span class="keyword">const</span> div = <span class="built_in">document</span>.createElement(<span class="string">&quot;div&quot;</span>);</span><br><span class="line">  div.className = <span class="string">&quot;c1&quot;</span>;</span><br><span class="line">  <span class="built_in">document</span>.body.appendChild(div);</span><br><span class="line">&#125;;</span><br></pre></td></tr></table></figure>

<figure class="highlight js"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// dom.test.js</span></span><br><span class="line"><span class="keyword">const</span> &#123; generateDiv &#125; = <span class="built_in">require</span>(<span class="string">&#x27;../dom&#x27;</span>) </span><br><span class="line"><span class="built_in">require</span>(<span class="string">&#x27;../jsdom-config&#x27;</span>)</span><br><span class="line">describe(<span class="string">&#x27;Dom测试&#x27;</span>, <span class="function">() =&gt;</span> &#123;</span><br><span class="line">    </span><br><span class="line">    test(<span class="string">&#x27;测试dom操作&#x27;</span>, <span class="function">() =&gt;</span> &#123;</span><br><span class="line">        generateDiv()</span><br><span class="line">        expect(<span class="built_in">document</span>.getElementsByClassName(<span class="string">&#x27;c1&#x27;</span>).length).toBe(<span class="number">1</span>)</span><br><span class="line">    &#125;)</span><br><span class="line">&#125;)</span><br></pre></td></tr></table></figure>





<h2 id="Vitest"><a href="#Vitest" class="headerlink" title="Vitest"></a>Vitest</h2><p>Vitest 是一个基于 Vite 的测试框架</p>
<ol>
<li>与 vite 同配置, 共用配置</li>
<li>兼容 Jest 的 API</li>
<li>使用 worker 线程并发执行, 效率高</li>
</ol>
<figure class="highlight js"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">pnpm i -D vitest@<span class="string">&quot;0.21.1&quot;</span> happy-dom@<span class="string">&quot;6.0.4&quot;</span> @vue/test-utils@<span class="string">&quot;2.0.2&quot;</span></span><br><span class="line"><span class="comment">// vitest ：测试框架，用于执行整个测试过程并提供断言库、mock、覆盖率</span></span><br><span class="line"><span class="comment">// happy-dom：是用于提供在 Node 环境中的 Dom 仿真模型</span></span><br><span class="line"><span class="comment">// @vue/test-utils 工具库： Vue推荐的测试工具库</span></span><br></pre></td></tr></table></figure>

<figure class="highlight js"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// vite.config.ts</span></span><br><span class="line"><span class="comment">/// &lt;reference types=&quot;vitest&quot; /&gt;</span></span><br><span class="line"><span class="keyword">export</span> <span class="keyword">default</span> defineConfig(&#123;</span><br><span class="line">    ...</span><br><span class="line">    test: &#123;</span><br><span class="line">        <span class="comment">// enable jest-like global test APIs</span></span><br><span class="line">        globals: <span class="literal">true</span>,</span><br><span class="line">        <span class="comment">// simulate DOM with happy-dom</span></span><br><span class="line">        <span class="comment">// (requires installing happy-dom as a peer dependency)</span></span><br><span class="line">        environment: <span class="string">&#x27;happy-dom&#x27;</span>,</span><br><span class="line">        <span class="comment">// 支持tsx组件，很关键</span></span><br><span class="line">        transformMode: &#123;</span><br><span class="line">            web: [<span class="regexp">/.[tj]sx$/</span>]</span><br><span class="line">        &#125;</span><br><span class="line">    &#125;</span><br><span class="line">&#125;)</span><br></pre></td></tr></table></figure>

<script type="text/javascript" src="https://cdn.jsdelivr.net/npm/kity@2.0.4/dist/kity.min.js"></script><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/kityminder-core@1.4.50/dist/kityminder.core.min.js"></script><script defer="true" type="text/javascript" src="https://cdn.jsdelivr.net/npm/hexo-simple-mindmap@0.2.0/dist/mindmap.min.js"></script><link rel="stylesheet" type="text/css" href="https://cdn.jsdelivr.net/npm/hexo-simple-mindmap@0.2.0/dist/mindmap.min.css">
      
    </div>

    
    
    

    <footer class="post-footer">
        <div class="post-eof"></div>
      
    </footer>
  </article>
</div>




    


<div class="post-block">
  
  

  <article itemscope itemtype="http://schema.org/Article" class="post-content" lang="">
    <link itemprop="mainEntityOfPage" href="https://gz1234.gitee.io/2022/07/09/%E5%89%8D%E7%AB%AF%E8%AE%B0%E5%BD%95%E6%95%B4%E7%90%86/Nuxt/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="image" content="/images/avatar.gif">
      <meta itemprop="name" content="郭泽">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="郭泽">
      <meta itemprop="description" content="">
    </span>

    <span hidden itemprop="post" itemscope itemtype="http://schema.org/CreativeWork">
      <meta itemprop="name" content="undefined | 郭泽">
      <meta itemprop="description" content="">
    </span>
      <header class="post-header">
        <h2 class="post-title" itemprop="name headline">
          <a href="/2022/07/09/%E5%89%8D%E7%AB%AF%E8%AE%B0%E5%BD%95%E6%95%B4%E7%90%86/Nuxt/" class="post-title-link" itemprop="url">Nuxt</a>
        </h2>

        <div class="post-meta-container">
          <div class="post-meta">
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-calendar"></i>
      </span>
      <span class="post-meta-item-text">发表于</span>

      <time title="创建时间：2022-07-09 02:21:52" itemprop="dateCreated datePublished" datetime="2022-07-09T02:21:52+08:00">2022-07-09</time>
    </span>
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-calendar-check"></i>
      </span>
      <span class="post-meta-item-text">更新于</span>
      <time title="修改时间：2023-04-25 16:49:18" itemprop="dateModified" datetime="2023-04-25T16:49:18+08:00">2023-04-25</time>
    </span>
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-folder"></i>
      </span>
      <span class="post-meta-item-text">分类于</span>
        <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
          <a href="/categories/%E5%89%8D%E7%AB%AF%E6%95%B4%E7%90%86/" itemprop="url" rel="index"><span itemprop="name">前端整理</span></a>
        </span>
    </span>

  
</div>

        </div>
      </header>

    
    
    
    <div class="post-body" itemprop="articleBody">
          <p>Nuxt.js是使用 Webpack 和 Node.js 进行封装的基于Vue的SSR框架</p>
<p>Nuxt3 整合vite + vue3 + composition api + ts、CLI、DevTools、Nuxt Kit表明，这是一个体系完备的通用开发框架，能提供良好的代码组织、极高的开发效率、开发体验和服务端渲染/静态网站生成（SSR/SSG）能力</p>
<h2 id="项目搭建"><a href="#项目搭建" class="headerlink" title="项目搭建"></a>项目搭建</h2><figure class="highlight js"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">npx nuxi init -t [v3/v2] nuxt-name</span><br><span class="line"><span class="comment">// 失败可以直接下载模板  https://github.com/nuxt/starter/tree/v3</span></span><br><span class="line"><span class="comment">// https://github.com/nuxt/starter/tree/v2</span></span><br></pre></td></tr></table></figure>

<h2 id="目录结构"><a href="#目录结构" class="headerlink" title="目录结构"></a>目录结构</h2><ul>
<li>.nuxt               // 自动生成的目录，用于展示结果</li>
<li>node_modules        // 项目依赖包存放目录</li>
<li>.gitignore          // Git的配置目录，比如一些文件不用Git管理就可以在这个文件中配置</li>
<li>app.vue             // 项目入口文件，你可以在这里配置路由的出口</li>
<li>nuxt.config.ts      // nuxt项目的配置文件 ，这个里边可以配置Nuxt项目的方法面面</li>
<li>package-lock.json   // 锁定安装时包的版本，以保证其他人在 npm install时和你保持一致</li>
<li>package.json        // 包的配置文件和项目的启动调式命令配置</li>
<li>README.md           // 项目的说明文件</li>
<li>tsconfig.json       // TypeScript的配置文件</li>
<li>pages               // 开发的页面目录</li>
<li>components          // 组件目录  可以直接名称调用</li>
<li>assets              // 静态资源目录</li>
<li>layouts             // 项目布局目录  <NuxtLayout name="def"></NuxtLayout></li>
<li>Composable          // 通用的业务逻辑代码，需要模块化管理,目录将自动导入 页面中直接使用，不能文件夹嵌套</li>
<li>middleware         // 中间件  </li>
<li>plugins            // plugins 目录中的文件并在创建 Vue 应用程序时加载它们 在文件名中使用 .server 或 .client 后缀来让插件在 服务器端 或 客户端 加载插件</li>
</ul>
<h2 id="Nuxt3-框架也提供一些内置的组件，常用的如下"><a href="#Nuxt3-框架也提供一些内置的组件，常用的如下" class="headerlink" title="Nuxt3 框架也提供一些内置的组件，常用的如下"></a>Nuxt3 框架也提供一些内置的组件，常用的如下</h2><ul>
<li><p>SEO组件： Html、Body、Head、Title、Meta、Style、Link、NoScript、Base</p>
</li>
<li><p>NuxtWelcome：欢迎页面组件，该组件是 @nuxt/ui的一部分</p>
</li>
<li><p>NuxtLayout：是 Nuxt 自带的页面布局组件</p>
</li>
<li><p>NuxtPage：是 Nuxt 自带的页面占位组件<br>需要显示位于目录中的顶级或嵌套页面 pages/；是对 router-view 的封装</p>
</li>
<li><p>ClientOnly：该组件中的默认插槽的内容只在客户端渲染 而fallback插槽的内容只在服务器端渲染</p>
</li>
<li><p>NuxtLink：是 Nuxt 自带的页面导航组件<br>是 Vue Router组件 和 HTMLa标签的封装。</p>
</li>
<li><p>NuxtChild 组件显示嵌套子组件内容已废弃  最新的使用NuxtPage</p>
</li>
<li><p>ClientOnly  专门用于仅在客户端渲染组件的组件</p>
<figure class="highlight js"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">&lt;template&gt;</span><br><span class="line">  &lt;div&gt;</span><br><span class="line">    &lt;ClientOnly&gt;</span><br><span class="line">      &lt;!-- 此组件仅在客户端显示 --&gt;</span><br><span class="line">      &lt;Comments /&gt;</span><br><span class="line">    &lt;/ClientOnly&gt;</span><br><span class="line">  &lt;/div&gt;</span><br><span class="line">&lt;/template&gt;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

</li>
</ul>
<h2 id="动态路由"><a href="#动态路由" class="headerlink" title="动态路由"></a>动态路由</h2><p>在文件名或者文件夹名称里面包含了方括号，它们将被转换为动态路由参数</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">-| pages&#x2F;</span><br><span class="line">---| users-[group]&#x2F;</span><br><span class="line">-----| [id].vue</span><br></pre></td></tr></table></figure>

<p>可以在组件[id].vue中访问group、id这两个参数:</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"> &lt;template&gt;</span><br><span class="line">  &#123;&#123; $route.params.group &#125;&#125;</span><br><span class="line">  &#123;&#123; $route.params.id &#125;&#125;</span><br><span class="line">&lt;&#x2F;template&gt;</span><br><span class="line"></span><br><span class="line">&lt;NuxtLink to&#x3D;&quot;&#x2F;users-admins&#x2F;123&quot;&gt;管理员123&lt;&#x2F;NuxtLink&gt;</span><br></pre></td></tr></table></figure>

<h2 id="嵌套路由"><a href="#嵌套路由" class="headerlink" title="嵌套路由"></a>嵌套路由</h2><p>目录和文件同名，就制造了嵌套路由。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br></pre></td><td class="code"><pre><span class="line">-| pages&#x2F;</span><br><span class="line">---| parent&#x2F;</span><br><span class="line">------| child.vue</span><br><span class="line">---| parent.vue</span><br><span class="line"></span><br><span class="line">&lt;template&gt;</span><br><span class="line">  &lt;div&gt;</span><br><span class="line">    &lt;h1&gt;child page&lt;&#x2F;h1&gt;</span><br><span class="line">  &lt;&#x2F;div&gt;</span><br><span class="line">&lt;&#x2F;template&gt;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"></span><br><span class="line">&lt;template&gt;</span><br><span class="line">  &lt;div&gt;</span><br><span class="line">    &lt;h1&gt;parent page&lt;&#x2F;h1&gt;</span><br><span class="line">    &lt;!-- 子组件出口 --&gt;</span><br><span class="line">    &lt;NuxtPage&gt;&lt;&#x2F;NuxtPage&gt;</span><br><span class="line">  &lt;&#x2F;div&gt;</span><br><span class="line">&lt;&#x2F;template&gt;</span><br><span class="line"></span><br><span class="line">&lt;NuxtLink to&#x3D;&quot;&#x2F;parent&#x2F;child&quot;&gt;Parent&lt;&#x2F;NuxtLink&gt;</span><br></pre></td></tr></table></figure>

<h2 id="Nuxt3-middleware路由中间件"><a href="#Nuxt3-middleware路由中间件" class="headerlink" title="Nuxt3 middleware路由中间件"></a>Nuxt3 middleware路由中间件</h2><p>新建一个middleware的文件夹，然后在文件下边新建一个文件default.global.ts 的文件。其中的.global代表这个中间件是全局的</p>
<figure class="highlight js"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">export</span> <span class="keyword">default</span> defineNuxtRouteMiddleware(<span class="function">(<span class="params">to, <span class="keyword">from</span></span>) =&gt;</span> &#123;</span><br><span class="line">  <span class="keyword">if</span> (to.path === <span class="string">&#x27;/demo1&#x27;</span>) &#123;</span><br><span class="line">     <span class="built_in">console</span>.log(<span class="string">&#x27;禁止访问这个页面&#x27;</span>)</span><br><span class="line">     abortNavigation()  <span class="comment">//停止当前导航，可以使用error进行报错</span></span><br><span class="line">     <span class="keyword">return</span>  navigateTo(<span class="string">&#x27;/&#x27;</span>)</span><br><span class="line">  &#125;</span><br><span class="line">&#125;)</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment">// 特定页面 去掉global</span></span><br><span class="line">&lt;template&gt;</span><br><span class="line">  &lt;div <span class="class"><span class="keyword">class</span></span>=<span class="string">&quot;&quot;</span>&gt;Demo7 Page&lt;/div&gt;</span><br><span class="line">&lt;/template&gt;</span><br><span class="line"></span><br><span class="line">&lt;script setup&gt;</span><br><span class="line">definePageMeta(&#123;</span><br><span class="line">  middleware: [<span class="string">&quot;default&quot;</span>],</span><br><span class="line">  <span class="comment">// or middleware: &#x27;auth&#x27;</span></span><br><span class="line">&#125;);</span><br><span class="line">&lt;/script&gt;</span><br><span class="line"></span><br><span class="line">&lt;style scoped&gt;&lt;/style&gt;</span><br></pre></td></tr></table></figure>

<script type="text/javascript" src="https://cdn.jsdelivr.net/npm/kity@2.0.4/dist/kity.min.js"></script><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/kityminder-core@1.4.50/dist/kityminder.core.min.js"></script><script defer="true" type="text/javascript" src="https://cdn.jsdelivr.net/npm/hexo-simple-mindmap@0.2.0/dist/mindmap.min.js"></script><link rel="stylesheet" type="text/css" href="https://cdn.jsdelivr.net/npm/hexo-simple-mindmap@0.2.0/dist/mindmap.min.css">
      
    </div>

    
    
    

    <footer class="post-footer">
        <div class="post-eof"></div>
      
    </footer>
  </article>
</div>




    


<div class="post-block">
  
  

  <article itemscope itemtype="http://schema.org/Article" class="post-content" lang="">
    <link itemprop="mainEntityOfPage" href="https://gz1234.gitee.io/2022/07/09/%E5%89%8D%E7%AB%AF%E8%AE%B0%E5%BD%95%E6%95%B4%E7%90%86/module&NPM/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="image" content="/images/avatar.gif">
      <meta itemprop="name" content="郭泽">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="郭泽">
      <meta itemprop="description" content="">
    </span>

    <span hidden itemprop="post" itemscope itemtype="http://schema.org/CreativeWork">
      <meta itemprop="name" content="undefined | 郭泽">
      <meta itemprop="description" content="">
    </span>
      <header class="post-header">
        <h2 class="post-title" itemprop="name headline">
          <a href="/2022/07/09/%E5%89%8D%E7%AB%AF%E8%AE%B0%E5%BD%95%E6%95%B4%E7%90%86/module&NPM/" class="post-title-link" itemprop="url">module&NPM</a>
        </h2>

        <div class="post-meta-container">
          <div class="post-meta">
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-calendar"></i>
      </span>
      <span class="post-meta-item-text">发表于</span>

      <time title="创建时间：2022-07-09 02:21:52" itemprop="dateCreated datePublished" datetime="2022-07-09T02:21:52+08:00">2022-07-09</time>
    </span>
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-calendar-check"></i>
      </span>
      <span class="post-meta-item-text">更新于</span>
      <time title="修改时间：2022-11-16 10:09:11" itemprop="dateModified" datetime="2022-11-16T10:09:11+08:00">2022-11-16</time>
    </span>
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-folder"></i>
      </span>
      <span class="post-meta-item-text">分类于</span>
        <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
          <a href="/categories/%E5%89%8D%E7%AB%AF%E6%95%B4%E7%90%86/" itemprop="url" rel="index"><span itemprop="name">前端整理</span></a>
        </span>
    </span>

  
</div>

        </div>
      </header>

    
    
    
    <div class="post-body" itemprop="articleBody">
          <h2 id="JS模块化方面的不足"><a href="#JS模块化方面的不足" class="headerlink" title="JS模块化方面的不足 #"></a>JS模块化方面的不足 <a href="#t01-js%E6%A8%A1%E5%9D%97%E5%8C%96%E6%96%B9%E9%9D%A2%E7%9A%84%E4%B8%8D%E8%B6%B3">#</a></h2><ul>
<li>JS没有模块系统，不支持封闭的作用域和依赖管理</li>
<li>没有标准库，没有文件系统和IO流API</li>
<li>也没有包管理系统</li>
</ul>
<h2 id="CommonJS规范"><a href="#CommonJS规范" class="headerlink" title="CommonJS规范 #"></a>CommonJS规范 <a href="#t12-commonjs%E8%A7%84%E8%8C%83">#</a></h2><ul>
<li>封装功能</li>
<li>封闭作用域</li>
<li>可能解决依赖问题</li>
<li>工作效率更高，重构方便</li>
</ul>
<h2 id="Node中的CommonJS"><a href="#Node中的CommonJS" class="headerlink" title="Node中的CommonJS #"></a>Node中的CommonJS <a href="#t23-node%E4%B8%AD%E7%9A%84commonjs">#</a></h2><ul>
<li>在node.js 里，模块划分所有的功能，每个JS都是一个模块</li>
<li>实现require方法,NPM实现了模块的自动加载和安装依赖</li></ul>
          <!--noindex-->
            <div class="post-button">
              <a class="btn" href="/2022/07/09/%E5%89%8D%E7%AB%AF%E8%AE%B0%E5%BD%95%E6%95%B4%E7%90%86/module&NPM/#more" rel="contents">
                阅读全文 &raquo;
              </a>
            </div>
          <!--/noindex-->
        
      
    </div>

    
    
    

    <footer class="post-footer">
        <div class="post-eof"></div>
      
    </footer>
  </article>
</div>




    


<div class="post-block">
  
  

  <article itemscope itemtype="http://schema.org/Article" class="post-content" lang="">
    <link itemprop="mainEntityOfPage" href="https://gz1234.gitee.io/2022/07/09/%E5%89%8D%E7%AB%AF%E8%AE%B0%E5%BD%95%E6%95%B4%E7%90%86/node/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="image" content="/images/avatar.gif">
      <meta itemprop="name" content="郭泽">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="郭泽">
      <meta itemprop="description" content="">
    </span>

    <span hidden itemprop="post" itemscope itemtype="http://schema.org/CreativeWork">
      <meta itemprop="name" content="undefined | 郭泽">
      <meta itemprop="description" content="">
    </span>
      <header class="post-header">
        <h2 class="post-title" itemprop="name headline">
          <a href="/2022/07/09/%E5%89%8D%E7%AB%AF%E8%AE%B0%E5%BD%95%E6%95%B4%E7%90%86/node/" class="post-title-link" itemprop="url">node</a>
        </h2>

        <div class="post-meta-container">
          <div class="post-meta">
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-calendar"></i>
      </span>
      <span class="post-meta-item-text">发表于</span>

      <time title="创建时间：2022-07-09 02:21:52" itemprop="dateCreated datePublished" datetime="2022-07-09T02:21:52+08:00">2022-07-09</time>
    </span>
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-calendar-check"></i>
      </span>
      <span class="post-meta-item-text">更新于</span>
      <time title="修改时间：2023-04-03 09:42:00" itemprop="dateModified" datetime="2023-04-03T09:42:00+08:00">2023-04-03</time>
    </span>
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-folder"></i>
      </span>
      <span class="post-meta-item-text">分类于</span>
        <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
          <a href="/categories/%E5%89%8D%E7%AB%AF%E6%95%B4%E7%90%86/" itemprop="url" rel="index"><span itemprop="name">前端整理</span></a>
        </span>
    </span>

  
</div>

        </div>
      </header>

    
    
    
    <div class="post-body" itemprop="articleBody">
          <h2 id="Node-能够解决什么问题"><a href="#Node-能够解决什么问题" class="headerlink" title="Node 能够解决什么问题? #"></a>Node 能够解决什么问题? <a href="#t01-node%E8%83%BD%E5%A4%9F%E8%A7%A3%E5%86%B3%E4%BB%80%E4%B9%88%E9%97%AE%E9%A2%98">#</a></h2><ul>
<li>Node 的首要目标是提供一种简单的，用于创建高性能服务器的开发工具</li>
<li>Web 服务器的瓶颈在于并发的用户量，对比 Java 和 Php 的实现方式</li>
</ul>
<h2 id="Node-是什么"><a href="#Node-是什么" class="headerlink" title="Node 是什么? #"></a>Node 是什么? <a href="#t12-node%E6%98%AF%E4%BB%80%E4%B9%88">#</a></h2><ul>
<li>Node.js 是一个基于 Chrome V8 引擎的 JavaScript 运行环境,让 JavaScript 的执行效率与低端的 C 语言的相近的执行效率。。</li>
<li>Node.js 使用了一个事件驱动、非阻塞式 I/O 的模型，使其轻量又高效。</li>
<li>Node.js 的包管理器 npm，是全球最大的开源库生态系统。</li>
</ul>
<h2 id="Node-特点"><a href="#Node-特点" class="headerlink" title="Node 特点 #"></a>Node 特点 <a href="#t23-node%E7%89%B9%E7%82%B9">#</a></h2>
          <!--noindex-->
            <div class="post-button">
              <a class="btn" href="/2022/07/09/%E5%89%8D%E7%AB%AF%E8%AE%B0%E5%BD%95%E6%95%B4%E7%90%86/node/#more" rel="contents">
                阅读全文 &raquo;
              </a>
            </div>
          <!--/noindex-->
        
      
    </div>

    
    
    

    <footer class="post-footer">
        <div class="post-eof"></div>
      
    </footer>
  </article>
</div>




    


<div class="post-block">
  
  

  <article itemscope itemtype="http://schema.org/Article" class="post-content" lang="">
    <link itemprop="mainEntityOfPage" href="https://gz1234.gitee.io/2022/07/09/%E5%89%8D%E7%AB%AF%E8%AE%B0%E5%BD%95%E6%95%B4%E7%90%86/mySql/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="image" content="/images/avatar.gif">
      <meta itemprop="name" content="郭泽">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="郭泽">
      <meta itemprop="description" content="">
    </span>

    <span hidden itemprop="post" itemscope itemtype="http://schema.org/CreativeWork">
      <meta itemprop="name" content="undefined | 郭泽">
      <meta itemprop="description" content="">
    </span>
      <header class="post-header">
        <h2 class="post-title" itemprop="name headline">
          <a href="/2022/07/09/%E5%89%8D%E7%AB%AF%E8%AE%B0%E5%BD%95%E6%95%B4%E7%90%86/mySql/" class="post-title-link" itemprop="url">sql</a>
        </h2>

        <div class="post-meta-container">
          <div class="post-meta">
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-calendar"></i>
      </span>
      <span class="post-meta-item-text">发表于</span>

      <time title="创建时间：2022-07-09 02:21:52" itemprop="dateCreated datePublished" datetime="2022-07-09T02:21:52+08:00">2022-07-09</time>
    </span>
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-calendar-check"></i>
      </span>
      <span class="post-meta-item-text">更新于</span>
      <time title="修改时间：2023-04-03 09:42:00" itemprop="dateModified" datetime="2023-04-03T09:42:00+08:00">2023-04-03</time>
    </span>
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-folder"></i>
      </span>
      <span class="post-meta-item-text">分类于</span>
        <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
          <a href="/categories/%E5%89%8D%E7%AB%AF%E6%95%B4%E7%90%86/" itemprop="url" rel="index"><span itemprop="name">前端整理</span></a>
        </span>
    </span>

  
</div>

        </div>
      </header>

    
    
    
    <div class="post-body" itemprop="articleBody">
          <figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*字符串*/</span></span><br><span class="line">char   <span class="comment">/*定长字符串*/</span></span><br><span class="line">varchar  <span class="comment">/*变长字符串*/</span></span><br><span class="line">text   <span class="comment">/*长文本*/</span></span><br><span class="line">blog   <span class="comment">/*无规则二进制字符串*/</span></span><br><span class="line"></span><br><span class="line"><span class="comment">/*整型 (常用tinyint(1)表示布尔型，1 为真，0为假)*/</span></span><br><span class="line">tinyint     <span class="comment">/*1字节 8位*/</span></span><br><span class="line">smallint    <span class="comment">/*2字节 16位*/</span></span><br><span class="line">mediumint   <span class="comment">/*3字节 24位*/</span></span><br><span class="line">int         <span class="comment">/*4字节 32位*/</span></span><br><span class="line">bigint      <span class="comment">/*8字节 64位*/</span></span><br><span class="line"></span><br><span class="line"><span class="comment">/*浮点型*/</span></span><br><span class="line">float  <span class="comment">/*(单精度) 4字节*/</span></span><br><span class="line">double  <span class="comment">/*(双精度) 8字节*/</span></span><br><span class="line">decimal  <span class="comment">/*精准类型, 其能指定储存精度，decimal(M,D), 其中M代表总位数，D代表小数位，M-D 代表整数位；比如金钱相关的计算就推荐使用decimal，否则会造成精度丢失问题，*/</span></span><br><span class="line"></span><br><span class="line"><span class="comment">/*日期时间*/</span></span><br><span class="line">date    <span class="comment">/*日期 通常就是 YYYY-MM-DD 格式*/</span></span><br><span class="line">time  <span class="comment">/*时间 格式 hh:mm:ss*/</span></span><br><span class="line">year  <span class="comment">/*年份 YYYY 不建议使用YY*/</span></span><br><span class="line">datetime <span class="comment">/*日期时间格式，其日期范围为 1001 至 9999 年，精度为秒 占用8位*/</span></span><br><span class="line">timestamp <span class="comment">/*挺多人称其为时间戳，其实是与Unix时间戳相同而已，从1970年1月1日午夜来表示秒数，最多储存至2038年。其依赖于时区，占用4位*/</span></span><br><span class="line"></span><br><span class="line"><span class="comment">/*枚举or集合*/</span></span><br><span class="line">enum(val1, val2, val3...) <span class="comment">/*储存固定值:其内部使用整型排序，显示时使用字符串，故在排序的时候可能会发生一些奇怪的现象，可以用field进行指定排序; 储存大小为16位*/</span></span><br><span class="line"><span class="keyword">set</span>(val1, val2, val3...) <span class="comment">/*类似数组:能存储多个值; 储存大小为64位*/</span></span><br><span class="line"></span><br><span class="line"><span class="comment">/*其他*/</span></span><br><span class="line"><span class="built_in">bit</span>   <span class="comment">/*储存位，坑很大；不建议使用*/</span></span><br><span class="line"><span class="comment">/*尽量不要使用外键，每次外键都会带来额外的性能开销*/</span></span><br></pre></td></tr></table></figure>

<h2 id="条件词"><a href="#条件词" class="headerlink" title="条件词"></a>条件词</h2><p>on:    过滤</p>
<p>where:   筛选</p>
<p>having:  只能在group by子句中出现的筛选</p>
<p><strong>详解</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">优先级: no -&gt; where -&gt; having</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"></span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">所有的查询都会产生一个中间临时报表，查询结果就是从返回临时报表中得到。</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"></span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">区别:</span></span><br><span class="line"><span class="comment"> on和where后面所跟限制条件的区别，主要与限制条件起作用的时机有关;</span></span><br><span class="line"><span class="comment"> on根据限制条件对数据库记录进行过滤，然后生产临时报表；</span></span><br><span class="line"><span class="comment"> where是在临时报表生产之后，根据限制条件从临时报表中筛选结果;</span></span><br><span class="line"><span class="comment">总结：</span></span><br><span class="line"><span class="comment"> 在左外连接中，on会返回左表中的所有记录；而where中，此时相当于inner join，只会返回满足条件的记录。</span></span><br><span class="line"><span class="comment"> </span></span><br><span class="line"><span class="comment">速度：</span></span><br><span class="line"><span class="comment"> 因为on限制条件发生时间较早，产生的临时报表数据集要小，因此on的性能要优于where。</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"></span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">区别:</span></span><br><span class="line"><span class="comment"> having和where的区别也是与限制条件起作用时机有关;</span></span><br><span class="line"><span class="comment"> having是在聚集函数计算结果出来之后筛选结果，查询结果只返回符合条件的分组，having不能单独出现，只能出现在group by子句中。</span></span><br><span class="line"><span class="comment"> where是在计算之前筛选结果，如果聚集函数使用where，那么聚集函数只计算满足where子句限制条件的数据。</span></span><br><span class="line"><span class="comment"> </span></span><br><span class="line"><span class="comment">总结：</span></span><br><span class="line"><span class="comment"> where即可以和select等其他子句搭配使用，也可以和group by子句搭配使用，where的优先级要高于having。</span></span><br><span class="line"><span class="comment"> </span></span><br><span class="line"><span class="comment">速度：</span></span><br><span class="line"><span class="comment"> 因为where在聚集函数之前筛选数据，having在计算之后筛选分组，因此where的性能要优于having</span></span><br><span class="line"><span class="comment">*/</span></span><br></pre></td></tr></table></figure>

<h2 id="运算符"><a href="#运算符" class="headerlink" title="运算符"></a>运算符</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br></pre></td><td class="code"><pre><span class="line">=  <span class="comment">/*等于*/</span></span><br><span class="line">&gt;  /*大于*/</span><br><span class="line">&lt;  /*小于*/</span><br><span class="line">&gt;=  /*大于等于*/</span><br><span class="line">&lt;=  /*小于等于*/</span><br><span class="line">&lt;&gt;  /*不等于*/</span><br><span class="line">!=  <span class="comment">/*不等于*/</span></span><br><span class="line">between min and max  <span class="comment">/*筛选min和max之间的*/</span></span><br><span class="line">is null     <span class="comment">/*是空值的*/</span></span><br><span class="line">is not null    <span class="comment">/*不是空值的*/</span></span><br><span class="line">like     <span class="comment">/*指定包含对应的字符, %代表任意数量的字符, _代表任意一个字符*/</span></span><br><span class="line">not like    <span class="comment">/*指定不包含对应的字符, %代表任意数量的字符, _代表任意一个字符*/</span></span><br><span class="line">in      <span class="comment">/*判断列的值在指定的范围内*/</span></span><br><span class="line">not in     <span class="comment">/*判断列的值不在指定的范围内*/</span></span><br><span class="line">and      <span class="comment">/*两边的表达式都为真,返回结果才为真*/</span></span><br><span class="line">&amp;&amp;      <span class="comment">/*两边的表达式都为真,返回结果才为真*/</span></span><br><span class="line">or      <span class="comment">/*两边的表达式有一项为真,返回结果就为真*/</span></span><br><span class="line">||      <span class="comment">/*两边的表达式有一项为真,返回结果就为真*/</span></span><br><span class="line">union all    <span class="comment">/*关联合并两条语句的结果*/</span></span><br><span class="line">xor      <span class="comment">/*异或,查出左真右假和左假右真的,不查出左真右真和左假右假的*/</span></span><br><span class="line">join     <span class="comment">/*多表关联*/</span></span><br><span class="line">on      <span class="comment">/*按指定条件过滤*/</span></span><br><span class="line">left join    <span class="comment">/*左外关联*/</span></span><br><span class="line">right join    <span class="comment">/*右外关联*/</span></span><br><span class="line">group by ... having  <span class="comment">/*把结果集按某些列分成不同的组,并对分组后的数据进行聚合操作*/</span></span><br><span class="line">as      <span class="comment">/*指定别名,也可做连接语句*/</span></span><br><span class="line">order by ... asc/desc <span class="comment">/*指定字段排序,asc升序/desc降序*/</span></span><br><span class="line">limit index, number  <span class="comment">/*分页,index为起始下标,number为记录条数*/</span></span><br><span class="line"></span><br><span class="line"><span class="comment">/*注意*/</span></span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">任何运算符和null值运算结果都为null</span></span><br><span class="line"><span class="comment">*/</span></span><br></pre></td></tr></table></figure>

<h2 id="聚合函数"><a href="#聚合函数" class="headerlink" title="聚合函数"></a>聚合函数</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">*表示所有</span></span><br><span class="line"><span class="comment">col_name列名</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line">count(*/col_name) /*统计总数*/</span><br><span class="line">sum(col_name)  <span class="comment">/*计算表中符合条件的数值列的合计值*/</span></span><br><span class="line">avg(col_name)  <span class="comment">/*计算表中符合条件的数值列的平均值*/</span></span><br><span class="line">max(col_name)  <span class="comment">/*计算表中符合条件的任意列中数据的最大值*/</span></span><br><span class="line">min(col_name)  <span class="comment">/*计算表中符合条件的任意列中数据的最小值*/</span></span><br></pre></td></tr></table></figure>

<h2 id="系统函数"><a href="#系统函数" class="headerlink" title="系统函数"></a>系统函数</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">curdate()   <span class="comment">/*返回当前日期*/</span></span><br><span class="line">curtime()   <span class="comment">/*返回当前时间*/</span></span><br><span class="line">now()    <span class="comment">/*返回当前的日期和时间*/</span></span><br><span class="line">date_format(date, fmt)  <span class="comment">/*按照fmt自定义的格式,对日期date进行格式化,fmt例:%Y%m%d %H:%i:%s*/</span></span><br><span class="line">sec_to_time(seconds) <span class="comment">/*把秒数转换为(时:分:秒)*/</span></span><br><span class="line">time_to_sec(time)  <span class="comment">/*把时间(时:分:秒)转换为秒数*/</span></span><br><span class="line">datediff(date1, date2) <span class="comment">/*返回date1和date2两个日期相差的天数*/</span></span><br><span class="line">unix_timestamp()  <span class="comment">/*返回unix时间戳*/</span></span><br><span class="line">from_unixtime()   <span class="comment">/*把unix时间戳转换为日期时间*/</span> </span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">时间单元unit:</span></span><br><span class="line"><span class="comment"> MICROSECOND   间隔单位：毫秒</span></span><br><span class="line"><span class="comment"> SECOND    间隔单位：秒</span></span><br><span class="line"><span class="comment"> MINUTE    间隔单位：分钟</span></span><br><span class="line"><span class="comment"> HOUR    间隔单位：小时</span></span><br><span class="line"><span class="comment"> DAY     间隔单位：天</span></span><br><span class="line"><span class="comment"> WEEK    间隔单位：星期</span></span><br><span class="line"><span class="comment"> MONTH    间隔单位：月</span></span><br><span class="line"><span class="comment"> QUARTER    间隔单位：季度</span></span><br><span class="line"><span class="comment"> YEAR    间隔单位：年</span></span><br><span class="line"><span class="comment"> SECOND_MICROSECOND 复合型，间隔单位：秒、毫秒，expr可以用两个值来分别指定秒和毫秒</span></span><br><span class="line"><span class="comment"> MINUTE_MICROSECOND 复合型，间隔单位：分、毫秒</span></span><br><span class="line"><span class="comment"> MINUTE_SECOND  复合型，间隔单位：分、秒</span></span><br><span class="line"><span class="comment"> HOUR_MICROSECOND 复合型，间隔单位：小时、毫秒</span></span><br><span class="line"><span class="comment"> HOUR_SECOND   复合型，间隔单位：小时、秒</span></span><br><span class="line"><span class="comment"> HOUR_MINUTE   复合型，间隔单位：小时、分</span></span><br><span class="line"><span class="comment"> DAY_MICROSECOND  复合型，间隔单位：天、毫秒</span></span><br><span class="line"><span class="comment"> DAY_SECOND   复合型，间隔单位：天、秒</span></span><br><span class="line"><span class="comment"> DAY_MINUTE   复合型，间隔单位：天、分</span></span><br><span class="line"><span class="comment"> DAY_HOUR   复合型，间隔单位：天、小时</span></span><br><span class="line"><span class="comment"> YEAR_MONTH   复合型，间隔单位：年、月</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line">date_add(date, interval expr unit) <span class="comment">/*对给定的日期增加或减少指定的时间单元*/</span></span><br><span class="line">extract(unit from date)    <span class="comment">/*返回日期date的指定部分*/</span></span><br><span class="line"></span><br><span class="line"><span class="comment">/*例:*/</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">now</span>(),</span><br><span class="line"> <span class="keyword">date_add</span>(<span class="keyword">now</span>(), <span class="built_in">interval</span> <span class="number">1</span> <span class="keyword">day</span>), <span class="comment">--当前时间加一天</span></span><br><span class="line"> <span class="keyword">date_add</span>(<span class="keyword">now</span>(), <span class="built_in">interval</span> <span class="number">1</span> <span class="keyword">year</span>), <span class="comment">--当前时间加一年</span></span><br><span class="line"> <span class="keyword">date_add</span>(<span class="keyword">now</span>(), <span class="built_in">interval</span> <span class="number">-1</span> <span class="keyword">day</span>), <span class="comment">--当前时减一天</span></span><br><span class="line"> <span class="keyword">date_add</span>(<span class="keyword">now</span>(), <span class="built_in">interval</span> <span class="string">&#x27;-1:30&#x27;</span> hour_minute), <span class="comment">--当前时间减1:30</span></span><br><span class="line"> <span class="keyword">extract</span>(<span class="keyword">year</span> <span class="keyword">from</span> <span class="keyword">now</span>()), <span class="comment">--提取当前时间的年份</span></span><br><span class="line"> <span class="keyword">extract</span>(<span class="keyword">month</span> <span class="keyword">from</span> <span class="keyword">now</span>()), <span class="comment">--提取当前时间的月份</span></span><br><span class="line"> </span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><span class="line">concat(str1,str2,...)   <span class="comment">/*把多个字符串拼接成一个字符串*/</span></span><br><span class="line">concat_ws(sep,str1,str2,...) <span class="comment">/*用指定的分割符sep连接字符串*/</span></span><br><span class="line">char_length(str)    <span class="comment">/*返回字符串的字符个数*/</span></span><br><span class="line">length(str)      <span class="comment">/*返回字符串的字节个数*/</span></span><br><span class="line">format(x,d,[locale])   <span class="comment">/*格式化数值x为0,000.00,并舍入到d位小数,locale语言环境是可选参数,默认为:en_US,可选:de_DE*/</span></span><br><span class="line">left(str,len)     <span class="comment">/*从左侧截取字符串str,截取len位字符*/</span></span><br><span class="line">right(str,len)     <span class="comment">/*从右侧截取字符串str,截取len位字符*/</span></span><br><span class="line">substring(str,index,[len])  <span class="comment">/*截取字符串str,指定起始下标index,可选截取长度len*/</span></span><br><span class="line">substring_index(str,delim,count)<span class="comment">/*返回字符串str按delim分割的前count个子字符串*/</span></span><br><span class="line">locate(substr,str)    <span class="comment">/*返回子串substr在str中第一次出现的位置*/</span></span><br><span class="line">trim([substr from] str)   <span class="comment">/*默认去除两端空格,可指定去除两端substr字符,[]表示可选参数*/</span></span><br><span class="line"></span><br><span class="line"><span class="comment">/*例:*/</span></span><br><span class="line"><span class="keyword">select</span> title,</span><br><span class="line"> <span class="keyword">locate</span>(<span class="string">&#x27;_&#x27;</span>, title),</span><br><span class="line"> <span class="keyword">substring</span>(title,<span class="number">1</span>,<span class="keyword">locate</span>(<span class="string">&#x27;-&#x27;</span>,title)),</span><br><span class="line"> <span class="keyword">substring</span>(title,<span class="number">1</span>,<span class="keyword">locate</span>(<span class="string">&#x27;-&#x27;</span>,title)<span class="number">-1</span>),</span><br><span class="line"> substring_index(title,<span class="string">&#x27;-&#x27;</span>)</span><br><span class="line"><span class="keyword">from</span> imc_course</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line">MD5(str)   <span class="comment">/*返回给str字符串MD5加密后的值*/</span></span><br><span class="line">rand()    <span class="comment">/*返回在0和1之间的随机值*/</span></span><br><span class="line">round(x,d)   <span class="comment">/*对数组x进行四舍五入保留d位小数*/</span></span><br><span class="line">case when   <span class="comment">/*多种情况返回不同的值*/</span></span><br><span class="line"><span class="comment">/*例:*/</span></span><br><span class="line"><span class="keyword">select</span> user_nick,</span><br><span class="line"> <span class="keyword">case</span> <span class="keyword">when</span> sex = <span class="number">1</span> <span class="keyword">then</span> <span class="string">&#x27;男&#x27;</span>,</span><br><span class="line">   <span class="keyword">when</span> sex = <span class="number">0</span> <span class="keyword">then</span> <span class="string">&#x27;女&#x27;</span>,</span><br><span class="line">   <span class="keyword">else</span> <span class="string">&#x27;未知&#x27;</span></span><br><span class="line"> <span class="keyword">end</span> <span class="keyword">as</span> <span class="string">&#x27;性别&#x27;</span></span><br><span class="line"><span class="keyword">from</span> imc_user</span><br><span class="line"><span class="keyword">where</span> <span class="keyword">case</span> <span class="keyword">when</span> sex = <span class="number">1</span> <span class="keyword">then</span> <span class="string">&#x27;男&#x27;</span>,</span><br><span class="line">   <span class="keyword">when</span> sex = <span class="number">0</span> <span class="keyword">then</span> <span class="string">&#x27;女&#x27;</span>,</span><br><span class="line">   <span class="keyword">else</span> <span class="string">&#x27;未知&#x27;</span></span><br><span class="line"> <span class="keyword">end</span> = <span class="string">&#x27;男&#x27;</span></span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">row_count()   <span class="comment">/*返回前一个SQL进行UPDATE，DELETE，INSERT操作所影响的行数*/</span></span><br></pre></td></tr></table></figure>

<h2 id="SQL种类"><a href="#SQL种类" class="headerlink" title="SQL种类"></a>SQL种类</h2><p>DCL:  数据库管理语句 </p>
<p>DDL:  数据库表定义语句</p>
<p>DML:  数据操作语句</p>
<p>TCL:    事务的控制语句</p>
<h3 id="DCL"><a href="#DCL" class="headerlink" title="DCL"></a>DCL</h3><p>建立数据库账号: create user</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">查看已安装的插件</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">show</span> plugins;</span><br><span class="line"></span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">mc_class 用户名</span></span><br><span class="line"><span class="comment">@&#x27;192.168.1.%&#x27; 允许访问控制的客户端IP</span></span><br><span class="line"><span class="comment">with &#x27;mysql_native_password&#x27; 选择密码加密方式</span></span><br><span class="line"><span class="comment">with max_user_connections 1 只允许一个线程连接</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">user</span> mc_class@<span class="string">&#x27;192.168.1.%&#x27;</span> <span class="keyword">identified</span> <span class="keyword">with</span> <span class="string">&#x27;mysql_native_password&#x27;</span> <span class="keyword">by</span> <span class="string">&#x27;123456&#x27;</span> <span class="keyword">with</span> max_user_connections <span class="number">1</span>;</span><br></pre></td></tr></table></figure>

<p>对用户授权: grant</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">查看所有支持的权限</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">show</span> <span class="keyword">privileges</span>\G</span><br><span class="line"></span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">给用户mc_class授予mysql.user表上的user和host列的查询权限</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">grant</span> <span class="keyword">select</span>(<span class="keyword">user</span>,host) <span class="keyword">on</span> mysql.user <span class="keyword">to</span> mc_class@<span class="string">&#x27;192.168.1.%&#x27;</span>; </span><br><span class="line"></span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">给用户mc_class授予mysql.user表上的所有列的查询权限</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">grant</span> <span class="keyword">select</span> <span class="keyword">on</span> mysql.user <span class="keyword">to</span> mc_class@<span class="string">&#x27;192.168.1.%&#x27;</span>; </span><br><span class="line"></span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">给用户mc_class授予mysql中所有表的查询权限</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">grant</span> <span class="keyword">select</span> <span class="keyword">on</span> mysql.* <span class="keyword">to</span> mc_class@<span class="string">&#x27;192.168.1.%&#x27;</span>; </span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">注意事项</span></span><br><span class="line"><span class="comment">1.使用grant授权的数据库账号必须存在</span></span><br><span class="line"><span class="comment">2.用户使用grant命令授权必须自身具有对应的权限</span></span><br><span class="line"><span class="comment">3.获取命令帮助 \h grant</span></span><br><span class="line"><span class="comment">*/</span></span><br></pre></td></tr></table></figure>

<p>回收用户权限: revoke</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">回收用户mc_class在mysql.user表上的user和host列的查询权限</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">revoke</span> <span class="keyword">select</span>(<span class="keyword">user</span>,host) <span class="keyword">on</span> mysql.user <span class="keyword">to</span> mc_class@<span class="string">&#x27;192.168.1.%&#x27;</span>; </span><br><span class="line"></span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">回收用户mc_class在mysql.user表上的所有列的查询权限</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">revoke</span> <span class="keyword">select</span> <span class="keyword">on</span> mysql.user <span class="keyword">to</span> mc_class@<span class="string">&#x27;192.168.1.%&#x27;</span>; </span><br><span class="line"></span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">回收用户mc_class在mysql中所有表的查询权限</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">revoke</span> <span class="keyword">select</span> <span class="keyword">on</span> mysql.* <span class="keyword">to</span> mc_class@<span class="string">&#x27;192.168.1.%&#x27;</span>; </span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">注意事项</span></span><br><span class="line"><span class="comment">1.使用revoke授权的数据库账号必须存在</span></span><br><span class="line"><span class="comment">2.用户使用revoke命令授权必须自身具有对应的权限</span></span><br><span class="line"><span class="comment">3.获取命令帮助 \h revoke</span></span><br><span class="line"><span class="comment">*/</span></span><br></pre></td></tr></table></figure>

<h3 id="DDL"><a href="#DDL" class="headerlink" title="DDL"></a>DDL</h3><p>建立/修改/删除数据库: create/alter/drop database</p>
<p>建立/修改/删除表: create/alter/drop table</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">unsigned 无符号</span></span><br><span class="line"><span class="comment">auto_increment 自增</span></span><br><span class="line"><span class="comment">not null 不为空</span></span><br><span class="line"><span class="comment">comment 备注信息</span></span><br><span class="line"><span class="comment">default 默认值</span></span><br><span class="line"><span class="comment">current_timestamp 当前时间戳</span></span><br><span class="line"><span class="comment">primary key 主键</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> imc_test (</span><br><span class="line"> <span class="keyword">id</span> <span class="built_in">int</span> <span class="keyword">unsigned</span> auto_increment <span class="keyword">not</span> <span class="literal">null</span> <span class="keyword">comment</span> <span class="string">&#x27;id&#x27;</span>,</span><br><span class="line">    user_id <span class="built_in">int</span> <span class="keyword">unsigned</span> <span class="keyword">not</span> <span class="literal">null</span> <span class="keyword">default</span> <span class="number">0</span> <span class="keyword">comment</span> <span class="string">&#x27;用户id&#x27;</span>,</span><br><span class="line">    user_name <span class="built_in">varchar</span> (<span class="number">50</span>) <span class="keyword">not</span> <span class="literal">null</span> <span class="keyword">default</span> <span class="string">&#x27;&#x27;</span> <span class="keyword">comment</span> <span class="string">&#x27;用户名&#x27;</span>,</span><br><span class="line">    user_gender enum (<span class="string">&#x27;男&#x27;</span>, <span class="string">&#x27;女&#x27;</span>) <span class="keyword">not</span> <span class="literal">null</span> <span class="keyword">default</span> <span class="string">&#x27;男&#x27;</span> <span class="keyword">comment</span> <span class="string">&#x27;用户性别&#x27;</span>,</span><br><span class="line">    user_content <span class="built_in">text</span> <span class="keyword">comment</span> <span class="string">&#x27;用户介绍&#x27;</span>,</span><br><span class="line">    add_time datetime <span class="keyword">not</span> <span class="literal">null</span> <span class="keyword">default</span> <span class="keyword">current_timestamp</span> <span class="keyword">comment</span> <span class="string">&#x27;创建时间&#x27;</span>,</span><br><span class="line">    primary <span class="keyword">key</span> (<span class="keyword">id</span>)</span><br><span class="line">) <span class="keyword">comment</span> <span class="string">&#x27;测试表&#x27;</span>;</span><br></pre></td></tr></table></figure>

<p>建立/删除索引: create/drop index</p>
<p>清空表: truncate table</p>
<p>重命名表: rename table</p>
<p>建立/修改/删除视图: create/alter/drop view</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">基于按条件查询出的a.course_id, a.title, b.class_name, c.type_name, d.level_name这些列的数据创建名为vm_course的视图</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">view</span> vm_course</span><br><span class="line"><span class="keyword">as</span></span><br><span class="line"><span class="keyword">select</span> a.course_id, a.title, b.class_name, c.type_name, d.level_name</span><br><span class="line"><span class="keyword">from</span> imc_course a</span><br><span class="line"><span class="keyword">join</span> imc_class b <span class="keyword">on</span> b.class_id = a.class_id</span><br><span class="line"><span class="keyword">join</span> imc_type c <span class="keyword">on</span> c.type_id = a.type_id</span><br><span class="line"><span class="keyword">join</span> imc_level d <span class="keyword">on</span> d.level_id = a.level_id</span><br></pre></td></tr></table></figure>

<h3 id="DML"><a href="#DML" class="headerlink" title="DML"></a>DML</h3><p>新增表中的数据:  insert into</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">查看表结构</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">show</span> <span class="keyword">create</span> <span class="keyword">table</span> imc_class</span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">新增数据前, 需先确认表结构, 哪些列不能为null, 哪些列可以为null, 不能为null的列是否有默认值;</span></span><br><span class="line"><span class="comment">插入的值需与每一列一一对应的</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">向imc_class表中的class_name字段插入多条数据,插入多条以逗号分隔;</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> imc_class(class_name) <span class="keyword">values</span>(<span class="string">&#x27;mysql&#x27;</span>),(<span class="string">&#x27;redis&#x27;</span>),(<span class="string">&#x27;mongoDB&#x27;</span>),(<span class="string">&#x27;安全测试&#x27;</span>),(<span class="string">&#x27;oracle&#x27;</span>) <span class="keyword">on</span> <span class="keyword">duplicate</span> <span class="keyword">key</span> <span class="keyword">update</span> add_time = <span class="keyword">current_time</span>;</span><br></pre></td></tr></table></figure>

<p>删除表中的数据:  delete</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">删除只存在于imc_course表中imc_course表和imc_chapter表中course_id相等并且imc_chapter表中course_id不为空的数据</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">delete</span> a</span><br><span class="line"><span class="keyword">from</span> imc_course a</span><br><span class="line"><span class="keyword">left</span> <span class="keyword">join</span> imc_chapter b <span class="keyword">on</span> b.course_id = a.course_id</span><br><span class="line"><span class="keyword">where</span> b.course_id <span class="keyword">is</span> <span class="literal">null</span></span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">查询imc_type表中以type_name分组并总数大于1的type_name,min_type_id,总数列的数据做为临时表</span></span><br><span class="line"><span class="comment">删除imc_type表中与临时表type_name相等并且type_id大于min_type_id的数据</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">delete</span> a</span><br><span class="line"><span class="keyword">from</span> imc_type a</span><br><span class="line"><span class="keyword">join</span> (</span><br><span class="line"> <span class="keyword">select</span> type_name, <span class="keyword">min</span>(type_id) <span class="keyword">as</span> min_type_id, <span class="keyword">count</span>(*)</span><br><span class="line">    <span class="keyword">from</span> imc_type</span><br><span class="line">    <span class="keyword">group</span> <span class="keyword">by</span> type_name <span class="keyword">having</span> <span class="keyword">count</span>(*) &gt; <span class="number">1</span></span><br><span class="line">) b</span><br><span class="line"><span class="keyword">on</span> a.type_name = b.type_name <span class="keyword">and</span> a.type_Id &gt; min_type_id</span><br><span class="line"></span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">给imc_type表的type_name建立唯一索引</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">unique</span> <span class="keyword">index</span> uqx_typename <span class="keyword">on</span> imc_tyoe(type_name)</span><br></pre></td></tr></table></figure>

<p>修改表中的数据:  update</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">修改imc_user表中user_nick为&#x27;太狼&#x27;的数据的user_status列为0</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">update</span> imc_user</span><br><span class="line"><span class="keyword">set</span> user_status = <span class="number">0</span></span><br><span class="line"><span class="keyword">where</span> user_nick = <span class="string">&#x27;太狼&#x27;</span></span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">修改imc_course表中随机排序后前10条记录的is_recommand列为1</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">update</span> imc_course</span><br><span class="line"><span class="keyword">set</span> is_recommand = <span class="number">1</span></span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span> <span class="keyword">rand</span>()</span><br><span class="line"><span class="keyword">limit</span> <span class="number">10</span></span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">按course_id分组查询出imc_classvalue表中course_id和4个评分列的平均值做为临时表</span></span><br><span class="line"><span class="comment">修改imc_course表与临时表的course_id相等的4个评分列的值为平均值</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">update</span> imc_course a</span><br><span class="line"><span class="keyword">join</span></span><br><span class="line">(</span><br><span class="line"> <span class="keyword">select</span> course_id,</span><br><span class="line">     <span class="keyword">avg</span>(content_score) <span class="keyword">as</span> avg_content,</span><br><span class="line">     <span class="keyword">avg</span>(level_score) <span class="keyword">as</span> avg_level,</span><br><span class="line">     <span class="keyword">avg</span>(logic_score) <span class="keyword">as</span> avg_logic,</span><br><span class="line">     <span class="keyword">avg</span>(score) <span class="keyword">as</span> avg_score</span><br><span class="line">    <span class="keyword">from</span> imc_classvalue</span><br><span class="line">    <span class="keyword">group</span> <span class="keyword">by</span> course_id</span><br><span class="line">) b <span class="keyword">on</span> a.course_id = b.course_id</span><br><span class="line"><span class="keyword">set</span> a.content_score = avg_content,</span><br><span class="line"> a.level_score = avg_level,</span><br><span class="line"> a.logic_score = avg_logic,</span><br><span class="line"> a.score = avg_score;</span><br></pre></td></tr></table></figure>

<p>查询表中的数据:  select</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">查询imc_db库中imc_class表的所有数据</span></span><br><span class="line"><span class="comment">星号表示所有</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> imc_db.<span class="string">&#x27;imc_class&#x27;</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">user_id指定要查询的列</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">select</span> user_id <span class="keyword">from</span> imc_db.<span class="string">&#x27;imc_class&#x27;</span>;</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">查询imc_course表中包含mysql字符的title列</span></span><br><span class="line"><span class="comment">%代表可以存在任意多个字符</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">select</span> title <span class="keyword">from</span> imc_course <span class="keyword">where</span> title <span class="keyword">like</span> <span class="string">&#x27;%mysql%&#x27;</span></span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">查询出imc_course表与imc_class表中class_id相等数据与imc_level表中level_id相等数据,按level_name与 class_name分组的level_name,class_name,总数三列并且总数大于3的数据</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">select</span> level_name, class_name, <span class="keyword">count</span>(*) </span><br><span class="line"><span class="keyword">from</span> imc_course a </span><br><span class="line"><span class="keyword">join</span> imc_class b <span class="keyword">on</span> b.class_id = a.class_id</span><br><span class="line"><span class="keyword">join</span> imc_level c <span class="keyword">on</span> c.level_id = a.level_id</span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> level_name, class_name</span><br><span class="line"><span class="keyword">having</span> <span class="keyword">count</span>(*) &gt; <span class="number">3</span></span><br></pre></td></tr></table></figure>

<h2 id="高级特性"><a href="#高级特性" class="headerlink" title="高级特性"></a>高级特性</h2><h3 id="CTE"><a href="#CTE" class="headerlink" title="CTE"></a>CTE</h3><p>全称: 公用表表达式CTE ( Common Table Expressions )</p>
<ul>
<li>8.0版本后才可使用</li>
<li>CTE生成一个命名临时表, 并且只在查询期间有效</li>
<li>CTE临时表在一个查询中可以多次引用和自引用</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">使用with开头, recursive可选参数(是否可以自引用)</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">with</span> <span class="keyword">recursive</span> cte <span class="keyword">as</span> (</span><br><span class="line"> <span class="keyword">select</span> title, study_cnt, class_id</span><br><span class="line">    <span class="keyword">from</span> imc_course</span><br><span class="line">    <span class="keyword">where</span> study_cnt&gt;<span class="number">2000</span></span><br><span class="line">    <span class="keyword">union</span> <span class="keyword">all</span></span><br><span class="line">    <span class="keyword">select</span> title, study_cnt, class_id</span><br><span class="line">    <span class="keyword">from</span> cte</span><br><span class="line">    <span class="keyword">where</span> study_cnt&lt;<span class="number">5000</span></span><br><span class="line">)</span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> cte</span><br></pre></td></tr></table></figure>

<h3 id="窗口函数"><a href="#窗口函数" class="headerlink" title="窗口函数"></a>窗口函数</h3><p>聚合函数都可以做为窗口函数使用</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">row_number()  <span class="comment">/*返回窗口分区内数据的行号*/</span></span><br><span class="line">rank()    <span class="comment">/*类似于row_number,对于相同数据会产生重复的行号,之后的数据行号会产生间隔*/</span></span><br><span class="line">dense_rank()  <span class="comment">/*类似于rank,区别在于当组内某行数据重复时,虽然行号会重复,但后续的行号不会产生间隔*/</span></span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">over内定义分组排序规则,partition by定义分组</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">select</span> study_name, class_name score,</span><br><span class="line"> row_number() <span class="keyword">over</span>(<span class="keyword">partition</span> <span class="keyword">by</span> class_name <span class="keyword">order</span> <span class="keyword">by</span> score <span class="keyword">desc</span>) <span class="keyword">as</span> rw,</span><br><span class="line"> <span class="keyword">rank</span>() <span class="keyword">over</span>(<span class="keyword">partition</span> <span class="keyword">by</span> class_name <span class="keyword">order</span> <span class="keyword">by</span> score <span class="keyword">desc</span>) <span class="keyword">as</span> rk,</span><br><span class="line"> <span class="keyword">dense_rank</span>() <span class="keyword">over</span>(<span class="keyword">partition</span> <span class="keyword">by</span> class_name <span class="keyword">order</span> <span class="keyword">by</span> score <span class="keyword">desc</span>) <span class="keyword">as</span> drk</span><br><span class="line"><span class="keyword">from</span> <span class="keyword">test</span></span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span> class_name, rw;</span><br></pre></td></tr></table></figure>

<h2 id="配置日志"><a href="#配置日志" class="headerlink" title="配置日志"></a>配置日志</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*开启慢查询日志,默认为off*/</span></span><br><span class="line"><span class="keyword">set</span> <span class="keyword">global</span> slow_query_log = <span class="keyword">on</span></span><br><span class="line"><span class="comment">/*指定日志存的路径,默认在mysql目录文件下*/</span></span><br><span class="line"><span class="keyword">set</span> <span class="keyword">global</span> slow_query_log_file = /sql_log/slowlog.log</span><br><span class="line"><span class="comment">/*sql执行时间超过设定的时间阈值就会被记录到日志中(单位:秒)*/</span></span><br><span class="line"><span class="keyword">set</span> <span class="keyword">global</span> long_query_time = xx.xxx</span><br><span class="line"><span class="comment">/*记录所有未使用索引的sql到日志中,默认为off*/</span></span><br><span class="line"><span class="keyword">set</span> <span class="keyword">global</span> log_queries_not_using_indexes = <span class="keyword">on</span></span><br></pre></td></tr></table></figure>

<p>分析日志工具</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*官方的分析工具(后面跟上日志文件名即可查看分析)*/</span></span><br><span class="line">mysqldumpslow</span><br><span class="line"><span class="comment">/*社区的分析工具(需要先下载安装,然后跟上日志文件名即可查看分析)*/</span></span><br><span class="line">pt-query-digest</span><br></pre></td></tr></table></figure>

<p>下载地址: <a target="_blank" rel="noopener" href="https://www.percona.com/downloads/percona-toolkit/LATEST/">https://www.percona.com/downloads/percona-toolkit/LATEST/</a></p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">wget https://downloads.percona.com/downloads/percona-toolkit/3.3.1/binary/redhat/7/x86_64/percona-toolkit-3.3.1-1.el7.x86_64.rpm</span><br></pre></td></tr></table></figure>

<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">/*安装依赖包*/</span><br><span class="line">yum install -y perl-DBD-MySQL.X86_64 perl-DBI.x86 perl-Time-HiRes.x86_64</span><br><span class="line">perl-IO-Socket-SSL.noarch perl-TermReadKey.x86_64 perl-Digest-MD5</span><br></pre></td></tr></table></figure>

<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">/*安装pt-query-digest*/</span><br><span class="line">rpm -ivh percona-toolkit-3.3.1-1.el7.x86_64.rpm</span><br><span class="line">/*列出工具集文件*/</span><br><span class="line">pt</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*查看长时间运行的SQL*/</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">id</span>, <span class="keyword">user</span>, host, DB, command, <span class="built_in">time</span>, state, info</span><br><span class="line"><span class="keyword">from</span> information_schema.PROCESSLIST</span><br></pre></td></tr></table></figure>

<h2 id="执行计划"><a href="#执行计划" class="headerlink" title="执行计划"></a>执行计划</h2><p>explain</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">explain</span></span><br><span class="line"><span class="keyword">select</span> course_id, title, study_cnt <span class="keyword">from</span> imc_course</span><br><span class="line"><span class="keyword">where</span> study_cnt &gt; <span class="number">3000</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">id</span>: <span class="number">1</span>       </span><br><span class="line"><span class="comment">/*id表示查询执行的顺序;id相同时由上到下执行;id不同时,由大到小执行*/</span></span><br><span class="line">select_type: simple</span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">simple    不包含子查询或union操作的查询</span></span><br><span class="line"><span class="comment">primary    查询中包含任何子查询,最外层的查询被标记为primary</span></span><br><span class="line"><span class="comment">subquery   select列表中的子查询</span></span><br><span class="line"><span class="comment">dependent subquery 依赖外部结果的子查询</span></span><br><span class="line"><span class="comment">union    union操作的第二个或之后的查询的值为union</span></span><br><span class="line"><span class="comment">dependent union  当union做为子查询时,第二或第二个后的查询的select_type值</span></span><br><span class="line"><span class="comment">union result  union产生的结果集</span></span><br><span class="line"><span class="comment">derived    出现在from子句中的子查询</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">table</span>: imc_course</span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">指明是从哪个表中获取数据</span></span><br><span class="line"><span class="comment">&lt;unionM,N&gt;由ID为M,N查询union产生的结果集</span></span><br><span class="line"><span class="comment">&lt;derived N&gt;/&lt;subquery N&gt;由ID为N的查询产生的结果集</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">partitions</span>: <span class="literal">null</span></span><br><span class="line"><span class="comment">/*是分区表则显示分区ID,不是则为null*/</span></span><br><span class="line"><span class="keyword">type</span>: <span class="keyword">all</span></span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">性能:</span></span><br><span class="line"><span class="comment">system   这是const联接类型的一个特例,当查询的表只有一行时使用</span></span><br><span class="line"><span class="comment">const   表中有且只有一个匹配的行时使用,对主键或唯一索引查询是效率最高的</span></span><br><span class="line"><span class="comment">eq_ref   唯一索引或主键查找,对于每个索引键,表中只有一条记录与之匹配</span></span><br><span class="line"><span class="comment">ref    非唯一索引查找,返回匹配某个单独值的所有行</span></span><br><span class="line"><span class="comment">ref_or_null  类似于ref类型的查询,但是附加了对null值列的查询</span></span><br><span class="line"><span class="comment">index_merge  该联接类型表示使用了索引合并优化方法</span></span><br><span class="line"><span class="comment">range   索引范围扫描,常见于between、&gt;、&lt;这样的查询条件</span></span><br><span class="line"><span class="comment">index   full index sacn全索引扫描,通all的区别是遍历的索引树</span></span><br><span class="line"><span class="comment">all    full table scan全表扫描,这是效率最差的联接方式</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line">possible_keys: <span class="literal">null</span>    </span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">指出查询中可能会用到的索引</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">key</span>: <span class="literal">null</span></span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">指出查询时实际用到的索引</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line">key_len: <span class="literal">null</span></span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">实际使用索引的最大长度</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">ref</span>: <span class="literal">null</span></span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">指出哪些列或常量被用于索引查找</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">rows</span>: <span class="number">100</span></span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">指出哪些列或常量被用于索引查找</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line">filtered: <span class="number">33.33</span></span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">表示返回结果的行数占需读取行数的百分比</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line">extra: <span class="keyword">using</span> <span class="keyword">where</span></span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">额外信息:</span></span><br><span class="line"><span class="comment">distinct   优化distinct操作,在找到第一匹配的元组后即停止找同样值的动作</span></span><br><span class="line"><span class="comment">not exists   使用not exists来优化查询</span></span><br><span class="line"><span class="comment">using filesort  使用文件来进行排序,通常会出现在order by或group by查询中</span></span><br><span class="line"><span class="comment">using index   使用了覆盖索引进行查询</span></span><br><span class="line"><span class="comment">using temporary  mysql需要使用临时表来查询,常见于排序、子查询和分组查询</span></span><br><span class="line"><span class="comment">using where   需要在mysql服务器层使用where条件来过滤数据</span></span><br><span class="line"><span class="comment">select tables optimized away 直接通过索引来获取数据,不用访问表</span></span><br><span class="line"><span class="comment">*/</span></span><br></pre></td></tr></table></figure>

<h2 id="SQL优化"><a href="#SQL优化" class="headerlink" title="SQL优化"></a>SQL优化</h2><p>优化方案</p>
<ul>
<li>优化SQL查询所涉及到的表中的索引</li>
<li>改写SQL以达到更好的利用索引的目的</li>
</ul>
<h3 id="索引"><a href="#索引" class="headerlink" title="索引"></a>索引</h3><p>告诉存储引擎如何快速的查找到所需要的数据</p>
<p>应该在什么列上建立索引</p>
<ul>
<li>where子句中的列</li>
<li>包含在order by、group by、distinct中的字段</li>
<li>多表join的关联列</li>
</ul>
<p>选择复合索引键的顺序</p>
<ul>
<li>区分度最高的列放在联合索引的最左侧</li>
<li>使用最频繁的列放到联合索引的最左侧</li>
<li>尽量把字段长度小的列放在联合索引列的最左侧</li>
</ul>
<p>索引使用误区</p>
<ul>
<li>索引越多越好 ( 纠正: 并不是 )</li>
<li>使用in列表查询不能用到索引 ( 纠正: 是可以用到索引的 )</li>
<li>查询过滤顺序必须用索引键顺序相同才可以使用索引 ( 纠正: 并不是 )</li>
</ul>
<h4 id="Innodb支持的索引类型"><a href="#Innodb支持的索引类型" class="headerlink" title="Innodb支持的索引类型"></a>Innodb支持的索引类型</h4><ul>
<li><p>Btree索引 ( 以B+树的结构存储索引数据 )</p>
<ul>
<li>特性</li>
</ul>
<ol>
<li>Btree索引适用于全值匹配的查询</li>
<li>Btree索引适合处理范围查找</li>
<li>Btree索引从索引的最左侧列开始匹配查找列</li>
</ol>
<ul>
<li>限制</li>
</ul>
<ol>
<li>只能从最左侧开始按索引键的顺序使用索引, 不能跳过索引键</li>
<li>not in和&lt;&gt;操作无法使用索引</li>
<li>索引列上不能使用表达式或是函数</li>
</ol>
</li>
<li><p>自适应HASH索引</p>
</li>
<li><p>全文索引</p>
</li>
<li><p>空间索引</p>
</li>
</ul>
<h3 id="改写"><a href="#改写" class="headerlink" title="改写"></a>改写</h3><h4 id="SQL改写的原则"><a href="#SQL改写的原则" class="headerlink" title="SQL改写的原则"></a>SQL改写的原则</h4><ul>
<li><p>使用 outer join 代替 not in</p>
</li>
<li><p>使用CTE代替子查询</p>
</li>
<li><p>拆分复杂的大SQL为多个简单的小SQL</p>
</li>
<li><p>通过计算列巧妙的优化</p>
</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*优化前*/</span></span><br><span class="line"><span class="keyword">explain</span></span><br><span class="line"><span class="keyword">select</span> * </span><br><span class="line"><span class="keyword">from</span> imc_classvalue</span><br><span class="line"><span class="keyword">where</span> (content_score + level_score + logic_score) &gt; <span class="number">28</span></span><br><span class="line"></span><br><span class="line"><span class="comment">/*使用计算列*/</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> imc_classvalue</span><br><span class="line"><span class="keyword">add</span> <span class="keyword">column</span> total_score <span class="built_in">decimal</span>(<span class="number">3</span>, <span class="number">1</span>) </span><br><span class="line"><span class="keyword">as</span> (content_score + level_score + logic_score)</span><br><span class="line"><span class="comment">/*给计算列添加索引*/</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">index</span> idx_totalScore <span class="keyword">on</span> imc_classvalue(total_score)</span><br><span class="line"></span><br><span class="line"><span class="comment">/*优化后*/</span></span><br><span class="line"><span class="keyword">explain</span></span><br><span class="line"><span class="keyword">select</span> * </span><br><span class="line"><span class="keyword">from</span> imc_classvalue</span><br><span class="line"><span class="keyword">where</span> total_score &gt; <span class="number">28</span></span><br></pre></td></tr></table></figure>

<h2 id="事务"><a href="#事务" class="headerlink" title="事务"></a>事务</h2><p>事务是数据库执行操作的最小逻辑单元</p>
<p>事务可以由一个SQL组成也可以由多个SQL组成</p>
<p>只能使用DML操作</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">start transaction    开启事务</span></span><br><span class="line"><span class="comment">begin       开启事务</span></span><br><span class="line"><span class="comment">commit       提交事务</span></span><br><span class="line"><span class="comment">rollback      回滚事务</span></span><br><span class="line"><span class="comment">*/</span></span><br></pre></td></tr></table></figure>

<h3 id="特性"><a href="#特性" class="headerlink" title="特性"></a>特性</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">原子性(A)  </span><br><span class="line"><span class="comment">/*一个事务中的所有操作,要么全部完成,要么全部不完成,不会结束在中间某个环节*/</span></span><br><span class="line">一致性(C)</span><br><span class="line"><span class="comment">/*在事务开始之前和事务结束之后,数据库的完整性没有被破坏*/</span></span><br><span class="line">隔离性(I)</span><br><span class="line"><span class="comment">/*事务的隔离性要求每个读写事务的对象与其它事务的操作对象能相互分离,即该事务提交前对其它事务都不可见*/</span></span><br><span class="line">持久性</span><br><span class="line"><span class="comment">/*事务一旦提交了,其结果就是永久性的,就算发送了宕机等事故,数据库也能将数据恢复*/</span></span><br></pre></td></tr></table></figure>

<h4 id="并发带来的问题"><a href="#并发带来的问题" class="headerlink" title="并发带来的问题"></a>并发带来的问题</h4><p>脏读</p>
<ul>
<li>一个事务读取了另一个事务未提交的数据</li>
</ul>
<p>不可重复读</p>
<ul>
<li>一个事务前后两次读取的同一数据不一致</li>
</ul>
<p>幻读</p>
<ul>
<li>指一个事务两次查询的结果集记录数不一致</li>
</ul>
<h3 id="隔离级别"><a href="#隔离级别" class="headerlink" title="隔离级别"></a>隔离级别</h3><p>InnoDB的事务隔离级别</p>
<table>
<thead>
<tr>
<th align="center">隔离级别</th>
<th align="center">脏读</th>
<th align="center">不可重复读</th>
<th align="center">幻读</th>
<th align="center">隔离性</th>
<th align="center">并发性</th>
</tr>
</thead>
<tbody><tr>
<td align="center">顺序读( serializable )</td>
<td align="center">N</td>
<td align="center">N</td>
<td align="center">N</td>
<td align="center">最高</td>
<td align="center">最低</td>
</tr>
<tr>
<td align="center">可重复读( repeatable read )</td>
<td align="center">N</td>
<td align="center">N</td>
<td align="center"><strong>N</strong></td>
<td align="center"></td>
<td align="center"></td>
</tr>
<tr>
<td align="center">读以提交( read committed )</td>
<td align="center">N</td>
<td align="center">Y</td>
<td align="center">Y</td>
<td align="center"></td>
<td align="center"></td>
</tr>
<tr>
<td align="center">读未提交( read uncommitted )</td>
<td align="center">Y</td>
<td align="center">Y</td>
<td align="center">Y</td>
<td align="center">最低</td>
<td align="center">最高</td>
</tr>
</tbody></table>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">设置事务的隔离级别</span></span><br><span class="line"><span class="comment">persist   所有连接都会生效,mysql服务重启也不会丢失</span></span><br><span class="line"><span class="comment">global   修改后,新的连接才会生效,mysql服务重启后会丢失</span></span><br><span class="line"><span class="comment">session   当前的连接,连接断开则会失效</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">set</span> <span class="keyword">session</span> <span class="keyword">transaction</span> <span class="keyword">isolation</span> <span class="keyword">level</span> <span class="keyword">serializable</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">/*查看事务隔离级别*/</span></span><br><span class="line"><span class="keyword">show</span> <span class="keyword">variables</span> <span class="keyword">like</span> <span class="string">&#x27;%iso%&#x27;</span></span><br></pre></td></tr></table></figure>

<h3 id="阻塞"><a href="#阻塞" class="headerlink" title="阻塞"></a>阻塞</h3><p>由于不同锁之间的兼容关系, 造成的一个事务需要等待另一个事务释放其所占用的资源的现象</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">查看阻塞情况</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">select</span> waiting_pid <span class="keyword">as</span> <span class="string">&#x27;被阻塞的线程&#x27;</span>,</span><br><span class="line"> waiting_query <span class="keyword">as</span> <span class="string">&#x27;被阻塞的sql&#x27;</span>,</span><br><span class="line"> blocking_pid <span class="keyword">as</span> <span class="string">&#x27;阻塞线程&#x27;</span>,</span><br><span class="line"> blocking_query <span class="keyword">as</span> <span class="string">&#x27;阻塞sql&#x27;</span>,</span><br><span class="line"> wait_age <span class="keyword">as</span> <span class="string">&#x27;阻塞时间&#x27;</span>,</span><br><span class="line"> sql_kill_blocking_query <span class="keyword">as</span> <span class="string">&#x27;建议操作&#x27;</span></span><br><span class="line"><span class="keyword">from</span> sys.innodb_lock_waits</span><br><span class="line"><span class="keyword">where</span> (<span class="keyword">unix_timestamp</span>() - <span class="keyword">unix_timestamp</span>(wait_started)) &gt; <span class="number">30</span></span><br></pre></td></tr></table></figure>

<h3 id="死锁"><a href="#死锁" class="headerlink" title="死锁"></a>死锁</h3><p>并行执行的多个事务相互之间占有了对方所需要的资源</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">开启死锁日志</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">set</span> <span class="keyword">global</span> innodb_print_all_deadlocks=<span class="keyword">on</span>;</span><br></pre></td></tr></table></figure>

<h2 id="存储过程"><a href="#存储过程" class="headerlink" title="存储过程"></a>存储过程</h2><p>语法:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">procedure</span> proName(<span class="keyword">in</span>|<span class="keyword">out</span>|inout parName parType)</span><br><span class="line">[characteristics]</span><br><span class="line"><span class="keyword">begin</span></span><br><span class="line"> <span class="comment">/*存储过程体*/</span></span><br><span class="line"><span class="keyword">end</span></span><br><span class="line"></span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">创建存储过程</span></span><br><span class="line"><span class="comment">proName  存储过程名称</span></span><br><span class="line"><span class="comment">parName  参数名</span></span><br><span class="line"><span class="comment">parType  参数类型</span></span><br><span class="line"><span class="comment">参数分类:</span></span><br><span class="line"><span class="comment">没有参数(无参数无返回值)</span></span><br><span class="line"><span class="comment">仅有in(有参数无返回)</span></span><br><span class="line"><span class="comment">仅有out(无参数有返回)</span></span><br><span class="line"><span class="comment">带inout(有参数有返回)</span></span><br><span class="line"><span class="comment">即带in又带out(有参数有返回)</span></span><br><span class="line"><span class="comment">注意: </span></span><br><span class="line"><span class="comment">in、out、inout都可以在一个存储过程中带多个</span></span><br><span class="line"><span class="comment">存储过程中可以有多条sql语句, 如果仅有一条sql语句, 可以省略begin和end</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">[characteristics]</span></span><br><span class="line"><span class="comment">表示创建存储过程时指定的对存储过程的约束条件, 取值信息如需下</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">language</span> <span class="keyword">sql</span>  </span><br><span class="line"><span class="comment">/*说明存储过程执行体是由SQL语句组成, 当前系统支持的语言是SQL*/</span></span><br><span class="line">[<span class="keyword">not</span>] <span class="keyword">deterministic</span></span><br><span class="line"><span class="comment">/*指明存储过程执行的结果是否确定。deterministic表示结果是确定的, 每次执行存储过程时, 相同的输入会得到相同的输出。not deterministic表示结果是不确定的, 相同的输入可能得到不同的输出。如果没有指定任意一个值, 默认为not deterministic*/</span></span><br><span class="line">&#123; contains <span class="keyword">sql</span> | <span class="keyword">no</span> <span class="keyword">sql</span> | <span class="keyword">reads</span> <span class="keyword">sql</span> <span class="keyword">data</span> | modifies <span class="keyword">sql</span> <span class="keyword">data</span> &#125;</span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">指明子程序使用sql语句的限制</span></span><br><span class="line"><span class="comment">contains sql: 表示当前存储过程的子程序包含sql语句, 但是并不包含读写数据的sql语句</span></span><br><span class="line"><span class="comment">no sql: 表示当前存储过程的子程序中不包含任何sql语句</span></span><br><span class="line"><span class="comment">reads sql data: 表示当前存储过程的子程序中包含读数据的sql语句</span></span><br><span class="line"><span class="comment">modifies sql data: 表示当前存储过程的子程序中包含写数据的sql语句</span></span><br><span class="line"><span class="comment">默认情况下, 系统会指定为contains sql</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">sql</span> <span class="keyword">security</span> &#123; definer | invoker &#125;</span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">执行当前存储过程的权限, 即指明哪些用户能够执行当前存储过程</span></span><br><span class="line"><span class="comment">definer表示只有当前存储过程的创建者或者定义者才能执行该存储过程</span></span><br><span class="line"><span class="comment">invoker表示拥有当前存储过程的访问权限的用户能够执行当前存储过程</span></span><br><span class="line"><span class="comment">如果没有设置相关值, 系统默认为definer</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">commit</span> <span class="string">&#x27;string&#x27;</span></span><br><span class="line"><span class="comment">/*注释信息, 可以用来描述存储过程*/</span></span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">delimiter 指定sql语句的结束符号</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line">delimiter $</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">procedure</span> select_all_data()</span><br><span class="line"><span class="keyword">begin</span></span><br><span class="line"> <span class="keyword">select</span> * <span class="keyword">from</span> emps;</span><br><span class="line"><span class="keyword">end</span> $</span><br><span class="line">delimiter ;</span><br><span class="line"></span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">调用存储过程</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">call</span> select_all_data();</span><br></pre></td></tr></table></figure>

<h3 id="优点"><a href="#优点" class="headerlink" title="优点"></a>优点</h3><ol>
<li>存储过程可以一次编译多次使用, 存储过程只在创建时进行编译, 之后的使用都不需要重新编译, 这就提升了sql的执行效率</li>
<li>可以减少开发工作量, 将代码封装成模块, 实际上是编程的核心思想之一, 这样可以把复杂的问题拆解成不同的模块, 然后模块之间可以重复使用, 在减少开发工作量的同时, 还能保证代理的结构清晰</li>
<li>存储过程的安全性强, 我们在设定存储过程的时候可以设置对用的使用权限, 这样就和视图一样具有较强的安全性</li>
<li>可以减少网络传输量, 因为代码封装到存储过程中, 每次使用只需要调用存储过程即可, 这样就减少了网络传输量</li>
<li>良好的封装性, 在进行相对复杂的数据库操作时, 原本需要使用一条一条的sql语句, 可能要连接多次数据库才能完成的操作, 现在变成了一次存储过程, 只需要连接一次即可</li>
</ol>
<h3 id="缺点"><a href="#缺点" class="headerlink" title="缺点"></a>缺点</h3><ol>
<li>可移植性差, 存储过程不能跨数据库移植, 如在MySql、Oracle和SQL Server里编写的存储过程, 在换成其他数据库时都需要重新编写</li>
<li>调试困难, 只有少数DBMS支持存储过程的调试, 对于复杂的存储过程来说, 开发和维护都不容易, 虽然也有一些第三方工具可以对存储过程进行调试, 但要收费</li>
<li>存储过程的版本管理很困难, 比如数据表索引发送变化了, 可能会导致存储过程失败, 我们在开发软件的时候往往需要进行版本管理, 但是存储过程本身没有版本控制, 版本迭代更的时候很麻烦</li>
<li>它不适合高并发的场景, 高并发的场景需要减少数据库的压力, 有时数据库会采用分库分表的方式, 而且对可扩展性要求很高, 在这种情况下, 存储过程会变得难以维护, 增加数据库的压力, 显然就不适用了</li>
</ol>
<h2 id="存储函数"><a href="#存储函数" class="headerlink" title="存储函数"></a>存储函数</h2><p>语法:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">function</span> funcName(parName parType)</span><br><span class="line"><span class="keyword">returns</span> retType</span><br><span class="line">[characteristics]</span><br><span class="line"><span class="keyword">begin</span></span><br><span class="line"> <span class="comment">/*函数体(必须有return)*/</span></span><br><span class="line"><span class="keyword">end</span></span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">funcName 函数名</span></span><br><span class="line"><span class="comment">parName  参数名</span></span><br><span class="line"><span class="comment">parType  参数类型</span></span><br><span class="line"><span class="comment">retType  返回值类型</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">[characteristics]</span></span><br><span class="line"><span class="comment">表示创建存储过程时指定的对存储过程的约束条件, 取值信息同存储过程一样</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">注意: </span></span><br><span class="line"><span class="comment">若创建存储函数报错&#x27;you might want to use the less safe log_bin_trust_function_creators variable&#x27;</span></span><br><span class="line"><span class="comment">解决: </span></span><br><span class="line"><span class="comment">1. 加上必要的函数约束&#x27;[not] deterministic&#x27;和&#x27;&#123; contains sql | no sql | reads sql data | modifies sql data &#125;&#x27;</span></span><br><span class="line"><span class="comment">2. set global log_bin_trust_function_creators = 1;</span></span><br><span class="line"><span class="comment">*/</span></span><br></pre></td></tr></table></figure>

<h2 id="查看"><a href="#查看" class="headerlink" title="查看"></a>查看</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">查看存储过程和存储函数的信息的方式</span></span><br><span class="line"><span class="comment">name  存储过程和函数的名称</span></span><br><span class="line"><span class="comment">&#123; procedure | function &#125; 表示procedure或者function</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">show</span> <span class="keyword">create</span> &#123; <span class="keyword">procedure</span> | <span class="keyword">function</span> &#125; <span class="keyword">name</span>;</span><br><span class="line"><span class="keyword">show</span> &#123; <span class="keyword">procedure</span> | <span class="keyword">function</span> &#125; <span class="keyword">status</span> <span class="keyword">like</span> <span class="string">&#x27;name&#x27;</span>;</span><br><span class="line"><span class="keyword">select</span> * <span class="keyword">from</span> information_schema.Routines</span><br><span class="line"><span class="keyword">where</span> ROUTINE_NAME=<span class="string">&#x27;name&#x27;</span> <span class="keyword">and</span> ROUTINE_TYPE = <span class="string">&#x27;&#123; procedure | function &#125;&#x27;</span>;</span><br></pre></td></tr></table></figure>

<h2 id="修改"><a href="#修改" class="headerlink" title="修改"></a>修改</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">alter</span> &#123; <span class="keyword">procedure</span> | <span class="keyword">function</span> &#125; <span class="keyword">name</span> </span><br><span class="line">[characteristics]</span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">修改存储过程和函数的约束特性</span></span><br><span class="line"><span class="comment">name  存储过程和函数的名称</span></span><br><span class="line"><span class="comment">&#123; procedure | function &#125; 表示procedure或者function</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">[characteristics]</span></span><br><span class="line"><span class="comment">表示创建存储过程时指定的对存储过程的约束条件, 取值信息如下</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line">&#123; contains <span class="keyword">sql</span> | <span class="keyword">no</span> <span class="keyword">sql</span> | <span class="keyword">reads</span> <span class="keyword">sql</span> <span class="keyword">data</span> | modifies <span class="keyword">sql</span> <span class="keyword">data</span> &#125;</span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">指明子程序使用sql语句的限制</span></span><br><span class="line"><span class="comment">contains sql: 表示当前存储过程的子程序包含sql语句, 但是并不包含读写数据的sql语句</span></span><br><span class="line"><span class="comment">no sql: 表示当前存储过程的子程序中不包含任何sql语句</span></span><br><span class="line"><span class="comment">reads sql data: 表示当前存储过程的子程序中包含读数据的sql语句</span></span><br><span class="line"><span class="comment">modifies sql data: 表示当前存储过程的子程序中包含写数据的sql语句</span></span><br><span class="line"><span class="comment">默认情况下, 系统会指定为contains sql</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">sql</span> <span class="keyword">security</span> &#123; definer | invoker &#125;</span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">执行当前存储过程的权限, 即指明哪些用户能够执行当前存储过程</span></span><br><span class="line"><span class="comment">definer表示只有当前存储过程的创建者或者定义者才能执行该存储过程</span></span><br><span class="line"><span class="comment">invoker表示拥有当前存储过程的访问权限的用户能够执行当前存储过程</span></span><br><span class="line"><span class="comment">如果没有设置相关值, 系统默认为definer</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">commit</span> <span class="string">&#x27;string&#x27;</span></span><br><span class="line"><span class="comment">/*注释信息, 可以用来描述存储过程*/</span></span><br></pre></td></tr></table></figure>

<h2 id="删除"><a href="#删除" class="headerlink" title="删除"></a>删除</h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">name  存储过程和函数的名称</span></span><br><span class="line"><span class="comment">&#123; procedure | function &#125; 表示procedure或者function</span></span><br><span class="line"><span class="comment">if exists 存储过程和函数的名称存在才执行删除</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">drop</span> &#123; <span class="keyword">procedure</span> | <span class="keyword">function</span> &#125; <span class="keyword">if</span> <span class="keyword">exists</span> <span class="keyword">name</span>;</span><br></pre></td></tr></table></figure>

<h2 id="变量"><a href="#变量" class="headerlink" title="变量"></a>变量</h2><h3 id="系统变量"><a href="#系统变量" class="headerlink" title="系统变量"></a>系统变量</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">#查看所以全局系统变量</span></span><br><span class="line"><span class="keyword">show</span> <span class="keyword">global</span> <span class="keyword">variables</span>;</span><br><span class="line"><span class="comment">#查看所以会话系统变量</span></span><br><span class="line"><span class="keyword">show</span> <span class="keyword">session</span> <span class="keyword">variables</span>;</span><br><span class="line">或</span><br><span class="line"><span class="keyword">show</span> <span class="keyword">variables</span>;</span><br><span class="line"><span class="comment">#查看符合条件的全部系统变量</span></span><br><span class="line"><span class="keyword">show</span> <span class="keyword">global</span> <span class="keyword">variables</span> <span class="keyword">like</span> <span class="string">&#x27;%标识符%&#x27;</span>;</span><br><span class="line"><span class="comment">#查看符合条件的会话系统变量</span></span><br><span class="line"><span class="keyword">show</span> <span class="keyword">session</span> <span class="keyword">variables</span> <span class="keyword">like</span> <span class="string">&#x27;%标识符%&#x27;</span>;</span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">查看指定系统变量</span></span><br><span class="line"><span class="comment">name 变量名称</span></span><br><span class="line"><span class="comment">[global | session]  全局或者会话,不指定默认先找会话系统变量</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">select</span> @@[<span class="keyword">global</span> | <span class="keyword">session</span>].name;</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">修改系统变量</span></span><br><span class="line"><span class="comment">1. 在my.ini文件里配置系统变量 (永久修改)</span></span><br><span class="line"><span class="comment">2. 使用set命令设置系统变量 (临时修改)</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">name 变量名称</span></span><br><span class="line"><span class="comment">value 变量值</span></span><br><span class="line"><span class="comment">[global | session]  全局或者会话,不指定默认先找会话系统变量</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">set</span> @@[<span class="keyword">global</span> | <span class="keyword">session</span>].name = <span class="keyword">value</span>;</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">#mysql8.0中新增了set persist命令</span></span><br><span class="line"><span class="keyword">set</span> persist <span class="keyword">global</span> max_connections = <span class="number">1000</span>;</span><br><span class="line"><span class="comment">#mysql会将该命令的配置保存到数据目录下的mysqld-auto.cnf文件中, 下次启动时会读取该文件, 用其中的配置来覆盖默认的配置文件</span></span><br></pre></td></tr></table></figure>

<h3 id="用户变量"><a href="#用户变量" class="headerlink" title="用户变量"></a>用户变量</h3><p>会话变量: 作用域和会话变量一样, 只对当前连接会话有效</p>
<p>局部变量: 只在begin和end语句块中有效, 局部变量只能在存储过程和函数中使用</p>
<h4 id="会话变量"><a href="#会话变量" class="headerlink" title="会话变量"></a>会话变量</h4><p>需要使用‘@’开头</p>
<p>语法:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">#创建赋值变量</span></span><br><span class="line"><span class="keyword">set</span> @<span class="keyword">name</span> = <span class="number">1</span>;</span><br><span class="line">或</span><br><span class="line"><span class="keyword">set</span> @<span class="keyword">name</span> := <span class="number">1</span>;</span><br><span class="line"><span class="comment">#使用sql语句方式创建赋值变量</span></span><br><span class="line"><span class="keyword">select</span> @<span class="keyword">name</span> := 表达式 [<span class="keyword">from</span>子句];</span><br><span class="line"><span class="keyword">select</span> 表达式 <span class="keyword">into</span> @<span class="keyword">name</span> [<span class="keyword">from</span>子句];</span><br><span class="line"><span class="comment">#例</span></span><br><span class="line"><span class="keyword">select</span> @<span class="keyword">count</span> := <span class="keyword">count</span>(*) <span class="keyword">from</span> <span class="keyword">empty</span>;</span><br><span class="line"><span class="keyword">select</span> <span class="keyword">avg</span>(salary) <span class="keyword">into</span> @avg_sal <span class="keyword">from</span> <span class="keyword">empty</span>;</span><br></pre></td></tr></table></figure>

<h4 id="局部变量"><a href="#局部变量" class="headerlink" title="局部变量"></a>局部变量</h4><p>需要使用declare声明</p>
<p>语法:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line">delimiter $</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">procedure</span> select_all_data()</span><br><span class="line"><span class="keyword">begin</span></span><br><span class="line"> <span class="comment">#声明局部变量</span></span><br><span class="line"> <span class="keyword">declare</span> a <span class="built_in">int</span> <span class="keyword">default</span> <span class="number">0</span>;</span><br><span class="line"> <span class="keyword">declare</span> b <span class="built_in">varchar</span>(<span class="number">25</span>) <span class="keyword">default</span> <span class="string">&#x27;高江华&#x27;</span>;</span><br><span class="line"> <span class="comment">#赋值</span></span><br><span class="line"> <span class="keyword">set</span> a = <span class="number">1</span>;</span><br><span class="line"> <span class="keyword">set</span> b := <span class="string">&#x27;灰太狼&#x27;</span>;</span><br><span class="line"> <span class="comment">#sql语句方式赋值</span></span><br><span class="line"> <span class="keyword">select</span> last_name <span class="keyword">into</span> b <span class="keyword">from</span> <span class="keyword">empty</span> <span class="keyword">where</span> empty_id = <span class="number">100</span>;</span><br><span class="line"> <span class="comment">#使用</span></span><br><span class="line"> <span class="keyword">select</span> a,b;</span><br><span class="line"><span class="keyword">end</span> $</span><br><span class="line">delimiter ;</span><br></pre></td></tr></table></figure>

<h2 id="定义条件与处理程序"><a href="#定义条件与处理程序" class="headerlink" title="定义条件与处理程序"></a>定义条件与处理程序</h2><p>定义条件是事先定义程序执行过程中可能遇到的问题, 处理程序定义了在遇到问题时应当采取的处理方式, 并且保证存储过程或函数在遇到警告或错误时能继续执行, 这样可以增强存储程序处理问题的能力, 避免程序异常停止运行</p>
<p>说明: 定义条件和处理程序在存储过程, 存储函数中都是支持的</p>
<p>案例:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">#创建存储过程</span></span><br><span class="line">delimiter $</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">procedure</span> UpdateDataNoCondition()</span><br><span class="line"> <span class="keyword">begin</span></span><br><span class="line">  <span class="keyword">set</span> @x = <span class="number">1</span>;</span><br><span class="line">  <span class="keyword">update</span> <span class="keyword">empty</span> <span class="keyword">set</span> email = <span class="literal">null</span> <span class="keyword">where</span> last_name = <span class="string">&#x27;灰太狼&#x27;</span>;</span><br><span class="line">  <span class="keyword">set</span> @x = <span class="number">2</span>;</span><br><span class="line">  <span class="keyword">update</span> <span class="keyword">empty</span> <span class="keyword">set</span> email = <span class="string">&#x27;598670138@qq.com&#x27;</span> <span class="keyword">where</span> last_name = <span class="string">&#x27;灰太狼&#x27;</span>;</span><br><span class="line">  <span class="keyword">set</span> @x = <span class="number">3</span>;</span><br><span class="line"> <span class="keyword">end</span> $</span><br><span class="line">delimiter ;</span><br><span class="line"><span class="comment">#调用存储过程</span></span><br><span class="line"><span class="keyword">call</span> UpdateDataNoCondition();</span><br><span class="line"><span class="comment">#报错信息</span></span><br><span class="line">Column &#x27;email&#x27; cannot be null</span><br><span class="line"><span class="comment">#查看变量@x为1</span></span><br><span class="line"><span class="keyword">select</span> @x;</span><br></pre></td></tr></table></figure>

<p>结论: 在存储过程中未定义条件和处理程序, 且当存储过程中执行的sql语句报错时, mysql数据库会抛出错误, 并退出当前sql逻辑, 不再向下继续执行。</p>
<h3 id="定义条件"><a href="#定义条件" class="headerlink" title="定义条件"></a>定义条件</h3><p>定义条件就是给mysql中的错误码命名, 这有助于存储的程序代码更清晰, 它将一个错误名字和指定的错误条件关联起来, 这个名字可以随后被用在定义处理程序的DECLARE HANDLER语句中</p>
<p>语法:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">declare</span> 错误名称 condition <span class="keyword">for</span> 错误码(或错误条件)</span><br></pre></td></tr></table></figure>

<p>错误码说明:</p>
<ul>
<li>MySQL_error_code和sqlstate_value都可以表示MySQL的错误<ul>
<li>MySQL_error_code是数值类型错误代码</li>
<li>sqlstate_value是长度为5的字符串类型错误代码</li>
</ul>
</li>
<li>例如: 在ERROR 1418 (HY000)中, 1418是MySQL_error_code, ‘HY000’是sqlstate_value</li>
<li>例如: 在ERROR 1142(42000)中, 1142是MySQL_error_code, ‘42000’是sqlstate_value</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">#使用MySQL_error_code</span></span><br><span class="line"><span class="keyword">declare</span> Field_Not_Be_NULL condition <span class="keyword">for</span> <span class="number">1048</span>;</span><br><span class="line"><span class="comment">#使用sqlstate_value</span></span><br><span class="line"><span class="keyword">declare</span> Field_Not_Be_NULL condition <span class="keyword">for</span> <span class="string">&#x27;23000&#x27;</span>;</span><br></pre></td></tr></table></figure>

<h3 id="定义处理程序"><a href="#定义处理程序" class="headerlink" title="定义处理程序"></a>定义处理程序</h3><p>可以为sql执行过程中发送的某个类型的错误定义特殊的处理程序, 定义处理程序时。</p>
<p>语法:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">declare</span> 处理方式 <span class="keyword">handler</span> <span class="keyword">for</span> 错误类型 处理语句</span><br></pre></td></tr></table></figure>

<ul>
<li>处理方式: 处理方式有3个取值: continue、exit、undo<ul>
<li>continue: 表示遇到错误不处理, 继续执行</li>
<li>exit: 表示遇到错误立即退出</li>
<li>undo: 表示遇到错误后撤回之前的操作, mysql中暂时不支持该操作</li>
</ul>
</li>
<li>错误类型( 即条件 ):<ul>
<li>sqlstate ‘字符串错误码’: 表示长度为5的sqlstate_value类型的错误代码</li>
<li>MySQL_error_code: 匹配数组类型错误代码</li>
<li>错误名称: 表示declare…condition定义的错误条件名称</li>
<li>sqlwarning: 匹配所有以01开头的sqlstate错误代码</li>
<li>not found: 匹配所有以02开头的sqlstate错误代码</li>
<li>SQLexception: 匹配所有没有被sqlwarning或not found捕获的sqlstate错误代码</li>
</ul>
</li>
<li>处理语句: 如果出现上述条件之一, 则采用对应的处理方式, 并执行指定的处理语句。语句可以是像”set 变量 = 值”这样的简单语句, 也可以是使用”begin … end”编写的复合语句</li>
</ul>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">#捕获sqlstate_value</span></span><br><span class="line"><span class="keyword">declare</span> continue <span class="keyword">handler</span> <span class="keyword">for</span> <span class="keyword">sqlstate</span> <span class="string">&#x27;42S02&#x27;</span> <span class="keyword">set</span> @info = <span class="string">&#x27;NO_SUCH_TABLE&#x27;</span>;</span><br><span class="line"><span class="comment">#捕获mysql_error_value</span></span><br><span class="line"><span class="keyword">declare</span> continue <span class="keyword">handler</span> <span class="keyword">for</span> <span class="number">1146</span> <span class="keyword">set</span> @info = <span class="string">&#x27;NO_SUCH_TABLE&#x27;</span>;</span><br><span class="line"><span class="comment">#先定义条件, 再调用</span></span><br><span class="line"><span class="keyword">declare</span> no_such_table condition <span class="keyword">for</span> <span class="number">1146</span>;</span><br><span class="line"><span class="keyword">declare</span> continue <span class="keyword">handler</span> <span class="keyword">for</span> no_such_table <span class="keyword">set</span> @info = <span class="string">&#x27;NO_SUCH_TABLE&#x27;</span>;</span><br><span class="line"><span class="comment">#使用sqlwarning</span></span><br><span class="line"><span class="keyword">declare</span> <span class="keyword">exit</span> <span class="keyword">handler</span> <span class="keyword">for</span> sqlwarning <span class="keyword">set</span> @info = <span class="string">&#x27;error&#x27;</span>;</span><br><span class="line"><span class="comment">#使用not found</span></span><br><span class="line"><span class="keyword">declare</span> <span class="keyword">exit</span> <span class="keyword">handler</span> <span class="keyword">for</span> <span class="keyword">not</span> <span class="keyword">found</span> <span class="keyword">set</span> @info = <span class="string">&#x27;NO_SUCH_TABLE&#x27;</span>;</span><br><span class="line"><span class="comment">#使用SQLexception</span></span><br><span class="line"><span class="keyword">declare</span> <span class="keyword">exit</span> <span class="keyword">handler</span> <span class="keyword">for</span> sqlexception <span class="keyword">set</span> @info = <span class="string">&#x27;error&#x27;</span>;</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">#存储过程方式</span></span><br><span class="line">delimiter $</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">procedure</span> UpdateDataNoCondition()</span><br><span class="line"> <span class="keyword">begin</span></span><br><span class="line">  <span class="comment">#声明处理程序</span></span><br><span class="line">  <span class="comment">#处理方式</span></span><br><span class="line">  <span class="keyword">declare</span> continue <span class="keyword">handler</span> <span class="keyword">for</span> <span class="number">1048</span> <span class="keyword">set</span> @prc_value = <span class="number">-1</span>;</span><br><span class="line">  </span><br><span class="line">  <span class="keyword">set</span> @x = <span class="number">1</span>;</span><br><span class="line">  <span class="keyword">update</span> <span class="keyword">empty</span> <span class="keyword">set</span> email = <span class="literal">null</span> <span class="keyword">where</span> last_name = <span class="string">&#x27;灰太狼&#x27;</span>;</span><br><span class="line">  <span class="keyword">set</span> @x = <span class="number">2</span>;</span><br><span class="line">  <span class="keyword">update</span> <span class="keyword">empty</span> <span class="keyword">set</span> email = <span class="string">&#x27;598670138@qq.com&#x27;</span> <span class="keyword">where</span> last_name = <span class="string">&#x27;灰太狼&#x27;</span>;</span><br><span class="line">  <span class="keyword">set</span> @x = <span class="number">3</span>;</span><br><span class="line"> <span class="keyword">end</span> $</span><br><span class="line">delimiter ;</span><br><span class="line"><span class="comment">#调用存储过程(可正常执行结束)</span></span><br><span class="line"><span class="keyword">call</span> UpdateDataNoCondition();</span><br><span class="line"><span class="comment">#查看@prc_value = -1</span></span><br><span class="line"><span class="keyword">select</span> @prc_value;</span><br><span class="line"><span class="comment">#查看@x = 3</span></span><br><span class="line"><span class="keyword">select</span> @x;</span><br></pre></td></tr></table></figure>

<h2 id="流程控制"><a href="#流程控制" class="headerlink" title="流程控制"></a>流程控制</h2><p>针对与mysql的流程控制语句主要有3类。注意: 只能用于存储程序。</p>
<ul>
<li>条件判断语句: if语句和case语句</li>
<li>循环语句: loop、while和repeat语句</li>
<li>跳转语句: iterate和leave语句</li>
</ul>
<p>if语法</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">if 表达式1 then 操作1</span><br><span class="line">[elseif 表达式2 then 操作2]...</span><br><span class="line">[else 操作n]</span><br><span class="line"><span class="keyword">end</span> <span class="keyword">if</span></span><br></pre></td></tr></table></figure>

<p>case语法</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">#类似switch</span></span><br><span class="line">case 表达式</span><br><span class="line">when 值1 then 结果1或语句1(如果是语句需要加分号)</span><br><span class="line">when 值2 then 结果2或语句2(如果是语句需要加分号)</span><br><span class="line">...</span><br><span class="line">else 结果n或语句n(如果是语句需要加分号)</span><br><span class="line"><span class="keyword">end</span> [<span class="keyword">case</span>] (如果放在<span class="string">&quot;begin...end&quot;</span>语句中需要加上<span class="keyword">case</span>, 如果放在<span class="keyword">select</span>后面不需要加)</span><br><span class="line"></span><br><span class="line"><span class="comment">#类似多重if</span></span><br><span class="line"><span class="keyword">case</span></span><br><span class="line"><span class="keyword">when</span> 条件<span class="number">1</span> <span class="keyword">then</span> 结果<span class="number">1</span>或语句<span class="number">1</span>(如果是语句需要加分号)</span><br><span class="line"><span class="keyword">when</span> 条件<span class="number">2</span> <span class="keyword">then</span> 结果<span class="number">2</span>或语句<span class="number">2</span>(如果是语句需要加分号)</span><br><span class="line">...</span><br><span class="line"><span class="keyword">else</span> 结果n或语句n(如果是语句需要加分号)</span><br><span class="line"><span class="keyword">end</span> [<span class="keyword">case</span>] (如果放在<span class="string">&quot;begin...end&quot;</span>语句中需要加上<span class="keyword">case</span>, 如果放在<span class="keyword">select</span>后面不需要加)</span><br></pre></td></tr></table></figure>

<p>loop语法</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">#loop_name: loop的标签名称</span></span><br><span class="line">[loop_name]:loop</span><br><span class="line"> 循环体</span><br><span class="line"><span class="keyword">end</span> <span class="keyword">loop</span> [loop_name]</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">#举例</span></span><br><span class="line"><span class="keyword">declare</span> <span class="keyword">id</span> <span class="built_in">int</span> <span class="keyword">default</span> <span class="number">0</span>;</span><br><span class="line">add_loop:loop</span><br><span class="line"> <span class="keyword">set</span> <span class="keyword">id</span> = <span class="keyword">id</span> + <span class="number">1</span>;</span><br><span class="line"> if id &gt;= 10 then leave add_loop;</span><br><span class="line"> <span class="keyword">end</span> <span class="keyword">if</span></span><br><span class="line"><span class="keyword">end</span> <span class="keyword">loop</span> add_loop;</span><br></pre></td></tr></table></figure>

<p>while语法</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">#while_name: while的标签名称</span></span><br><span class="line">[while_name]:while 循环条件 <span class="keyword">do</span></span><br><span class="line"> 循环体</span><br><span class="line"><span class="keyword">end</span> <span class="keyword">while</span> [while_name];</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">#举例</span></span><br><span class="line">delimiter $</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">procedure</span> test_while()</span><br><span class="line"> <span class="keyword">begin</span></span><br><span class="line">  <span class="comment">#初始化条件</span></span><br><span class="line">  <span class="keyword">declare</span> <span class="keyword">num</span> <span class="built_in">int</span> <span class="keyword">default</span> <span class="number">1</span>;</span><br><span class="line">  <span class="comment">#循环条件</span></span><br><span class="line">  while num &lt;=10 do</span><br><span class="line">   <span class="comment">#循环体</span></span><br><span class="line">   <span class="keyword">insert</span> <span class="keyword">into</span> imc_class(class_num) <span class="keyword">values</span> (class_num + <span class="number">1000</span>);</span><br><span class="line">   <span class="comment">#迭代条件</span></span><br><span class="line">   <span class="keyword">set</span> <span class="keyword">num</span> = <span class="keyword">num</span> + <span class="number">1</span>;</span><br><span class="line">  <span class="keyword">end</span> <span class="keyword">while</span>;</span><br><span class="line"> <span class="keyword">end</span> $</span><br><span class="line">delimiter ;</span><br></pre></td></tr></table></figure>

<p>repeat语法</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">#repeat_name: repeat的标签名称</span></span><br><span class="line">[repeat_name]:repeat</span><br><span class="line"> 循环体</span><br><span class="line">until 结束循环的条件表达式</span><br><span class="line"><span class="keyword">end</span> <span class="keyword">while</span> [repeat_name];</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">#举例</span></span><br><span class="line">delimiter $</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">procedure</span> test_repeat()</span><br><span class="line"> <span class="keyword">begin</span></span><br><span class="line">  <span class="comment">#初始化条件</span></span><br><span class="line">  <span class="keyword">declare</span> <span class="keyword">num</span> <span class="built_in">int</span> <span class="keyword">default</span> <span class="number">1</span>;</span><br><span class="line">  <span class="comment">#循环条件</span></span><br><span class="line">  repeat</span><br><span class="line">   <span class="comment">#循环体</span></span><br><span class="line">   <span class="keyword">insert</span> <span class="keyword">into</span> imc_class(class_num) <span class="keyword">values</span> (class_num + <span class="number">1000</span>);</span><br><span class="line">   <span class="comment">#迭代条件</span></span><br><span class="line">   <span class="keyword">set</span> <span class="keyword">num</span> = <span class="keyword">num</span> + <span class="number">1</span>;</span><br><span class="line">  until num &gt;=10</span><br><span class="line">  <span class="keyword">end</span> <span class="keyword">repeat</span>;</span><br><span class="line"> <span class="keyword">end</span> $</span><br><span class="line">delimiter ;</span><br></pre></td></tr></table></figure>

<p>leave语法</p>
<p>可以用在循环语句内, 或者是”begin…end”包裹的程序体内, 表示跳出循环或跳出程序体的操作, 类似break。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">#举例</span></span><br><span class="line">delimiter $</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">procedure</span> leave_begin(<span class="keyword">in</span> <span class="keyword">num</span> <span class="built_in">int</span>)</span><br><span class="line"> begin_label:<span class="keyword">begin</span></span><br><span class="line">  <span class="keyword">if</span> <span class="keyword">num</span> &lt;= <span class="number">0</span></span><br><span class="line">   <span class="keyword">then</span> leave begin_label;</span><br><span class="line">  elseif num = 1</span><br><span class="line">   then <span class="keyword">select</span> <span class="keyword">avg</span>(salary) <span class="keyword">from</span> <span class="keyword">empty</span>;</span><br><span class="line">  elseif num = 2</span><br><span class="line">   then <span class="keyword">select</span> <span class="keyword">min</span>(salary) <span class="keyword">from</span> <span class="keyword">empty</span>;</span><br><span class="line">  else </span><br><span class="line">   <span class="keyword">select</span> <span class="keyword">max</span>(salary) <span class="keyword">from</span> <span class="keyword">empty</span>;</span><br><span class="line">  <span class="keyword">end</span> <span class="keyword">if</span>;</span><br><span class="line">  <span class="comment">#查询总人数</span></span><br><span class="line">  <span class="keyword">select</span> <span class="keyword">count</span>(*) <span class="keyword">from</span> <span class="keyword">empty</span></span><br><span class="line"> <span class="keyword">end</span> $</span><br><span class="line">delimiter ;</span><br><span class="line"><span class="comment">#调用(传入0直接跳出)</span></span><br><span class="line"><span class="keyword">call</span> leave_begin(<span class="number">0</span>)</span><br></pre></td></tr></table></figure>

<p>iterate语法</p>
<p>只能用在循环语句内, 表示重新开始循环, 将执行顺序转到语句段开头处, 进行下一次循环, 类似continue</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">#举例</span></span><br><span class="line">delimiter $</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">procedure</span> test_iterate()</span><br><span class="line"> <span class="keyword">begin</span></span><br><span class="line">  <span class="keyword">declare</span> <span class="keyword">num</span> <span class="built_in">int</span> <span class="keyword">default</span> <span class="number">0</span>;</span><br><span class="line">  loop_name:loop</span><br><span class="line">   <span class="comment">#赋值</span></span><br><span class="line">   <span class="keyword">set</span> <span class="keyword">num</span> = <span class="keyword">num</span> + <span class="number">1</span>;</span><br><span class="line">   if num &lt; 10</span><br><span class="line">    then iterate loop_name;</span><br><span class="line">   elseif num &gt; 15</span><br><span class="line">    then leave loop_name;</span><br><span class="line">   <span class="keyword">end</span> <span class="keyword">if</span>;</span><br><span class="line">   <span class="keyword">select</span> <span class="string">&#x27;灰太狼&#x27;</span></span><br><span class="line">  <span class="keyword">end</span> <span class="keyword">loop</span>;</span><br><span class="line"> <span class="keyword">end</span> $</span><br><span class="line">delimiter ;</span><br><span class="line"><span class="comment">#调用(num为10-15为输出&#x27;灰太狼&#x27;,一共6次)</span></span><br><span class="line"><span class="keyword">call</span> test_iterate()</span><br></pre></td></tr></table></figure>

<h2 id="游标"><a href="#游标" class="headerlink" title="游标"></a>游标</h2><p>游标必须在声明处理程序之前被声明, 并且变量和条件还必须在声明游标或处理程序之前被声明</p>
<p>创建游标:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">#适用于mysql, sql server, DB2和MariaDB</span></span><br><span class="line"><span class="keyword">declare</span> cursor_name <span class="keyword">cursor</span> <span class="keyword">for</span> select_statement;</span><br><span class="line"><span class="comment">#适用于Oracle和PostgreSQL</span></span><br><span class="line"><span class="keyword">declare</span> cursor_name <span class="keyword">cursor</span> <span class="keyword">is</span> select_statement;</span><br><span class="line"><span class="comment">#select_statement是select语句返回用于创建游标的结果集</span></span><br></pre></td></tr></table></figure>

<p>打开游标:</p>
<p>定义游标后, 若要使用游标, 必须先打开游标, 打开后select语句的查询结果集就会送到游标工作区。</p>
<p>为后面游标的逐条读取结果集中的记录做准备</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">open cursor_name;</span><br></pre></td></tr></table></figure>

<p>使用游标:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">#使用cursor_name游标读取当前行, 将数据保存到var_name变量中, 若当前行有多个列, 则在into后面添加多个变量接收, 此时游标指针指到下一行</span></span><br><span class="line">fetch cursor_name into var_name [, var_name]...</span><br></pre></td></tr></table></figure>

<p>关闭游标:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">close cursor_name;</span><br></pre></td></tr></table></figure>

<p>举例:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br></pre></td><td class="code"><pre><span class="line">delimiter $</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">procedure</span> get_count_bt_limit_total_salary(<span class="keyword">in</span> limit_total_salary <span class="keyword">double</span>, <span class="keyword">out</span> total_count <span class="built_in">int</span>)</span><br><span class="line"> <span class="keyword">begin</span></span><br><span class="line">  <span class="comment">#声明局部变量</span></span><br><span class="line">  <span class="keyword">declare</span> sum_sal <span class="keyword">double</span> <span class="keyword">default</span> <span class="number">0.0</span>; <span class="comment">#记录累加的工资总额</span></span><br><span class="line">  <span class="keyword">declare</span> emp_sal <span class="keyword">double</span>; <span class="comment">#记录每个员工的工资</span></span><br><span class="line">  <span class="keyword">declare</span> emp_count <span class="built_in">int</span> <span class="keyword">default</span> <span class="number">0</span>; <span class="comment">#记录累加的人数</span></span><br><span class="line">  <span class="comment">#声明游标</span></span><br><span class="line">  <span class="keyword">declare</span> emp_cursor <span class="keyword">cursor</span> <span class="keyword">for</span> <span class="keyword">select</span> salary <span class="keyword">from</span> employees <span class="keyword">order</span> <span class="keyword">by</span> salary <span class="keyword">desc</span>;</span><br><span class="line">  <span class="comment">#打开游标</span></span><br><span class="line">  open emp_cursor;</span><br><span class="line">  repeat</span><br><span class="line">   <span class="comment">#使用游标</span></span><br><span class="line">   fetch emp_cursor into emp_sal;</span><br><span class="line">   <span class="keyword">set</span> sum_sal = sum_sal + emp_sal;</span><br><span class="line">   <span class="keyword">set</span> emp_count = emp_count + <span class="number">1</span>;</span><br><span class="line">   until sum_sal &gt;= limit_total_salary</span><br><span class="line">  <span class="keyword">end</span> <span class="keyword">repeat</span>;</span><br><span class="line">  <span class="keyword">set</span> total_count = emp_count;</span><br><span class="line">  <span class="comment">#关闭游标</span></span><br><span class="line">  close emp_cursor;</span><br><span class="line"> <span class="keyword">end</span> $</span><br><span class="line">delimiter ;</span><br><span class="line"><span class="comment">#调用</span></span><br><span class="line"><span class="keyword">call</span> get_count_bt_limit_total_salary(<span class="number">200000</span>, @total_count)</span><br><span class="line"><span class="comment">#获取@total_count</span></span><br><span class="line"><span class="keyword">select</span> @total_count;</span><br></pre></td></tr></table></figure>

<p>小结:</p>
<p>游标的使用过程中会对数据进行加锁, 在业务并发量大的时候, 不仅影响业务效率, 还会消耗系统资源, 造成内存不足, 因为游标是在内存中进行的处理。</p>
<p>建议: 用完之后一定记得关闭游标</p>
<h2 id="触发器"><a href="#触发器" class="headerlink" title="触发器"></a>触发器</h2><p>创建触发器:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">brfore  表示在事件之前触发</span></span><br><span class="line"><span class="comment">after  表示在事件之后触发</span></span><br><span class="line"><span class="comment">insert  表示插入记录时触发</span></span><br><span class="line"><span class="comment">update  表示更新记录时触发</span></span><br><span class="line"><span class="comment">delete  表示删除记录时触发</span></span><br><span class="line"><span class="comment">触发器执行的语句块  可以是单条sql也可以是&#x27;begin...end&#x27;复合语句块</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">trigger</span> 触发器名称</span><br><span class="line">&#123;brfore|<span class="keyword">after</span>&#125; &#123;<span class="keyword">insert</span>|<span class="keyword">update</span>|<span class="keyword">delete</span>&#125; <span class="keyword">on</span> 表名</span><br><span class="line"><span class="keyword">for</span> <span class="keyword">each</span> <span class="keyword">row</span></span><br><span class="line">触发器执行的语句块</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">#例</span></span><br><span class="line">delimiter $</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">trigger</span> before_inset_test_tri</span><br><span class="line"><span class="keyword">before</span> <span class="keyword">insert</span> <span class="keyword">on</span> test_trigger</span><br><span class="line"><span class="keyword">for</span> <span class="keyword">each</span> <span class="keyword">row</span></span><br><span class="line"><span class="keyword">begin</span></span><br><span class="line"> <span class="keyword">insert</span> <span class="keyword">into</span> test_trigger_log(t_log)</span><br><span class="line"> <span class="keyword">values</span>(<span class="string">&#x27;before insert ...&#x27;</span>)</span><br><span class="line"><span class="keyword">end</span> $</span><br><span class="line">delimiter ;</span><br></pre></td></tr></table></figure>

<p>查看触发器:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">#查看当前数据库里所有触发器的定义</span></span><br><span class="line"><span class="keyword">show</span> <span class="keyword">trigger</span>\G;</span><br><span class="line"><span class="comment">#查看当前数据库中某个触发器的定义</span></span><br><span class="line"><span class="keyword">show</span> <span class="keyword">create</span> <span class="keyword">trigger</span> 触发器名称;</span><br><span class="line"><span class="comment">#从系统库information_schma的trigger表中查询&quot;salary_check_trigger&quot;触发器的信息</span></span><br><span class="line"><span class="keyword">show</span> * <span class="keyword">from</span> information_schma.triggers;</span><br></pre></td></tr></table></figure>

<p>删除触发器:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">#触发器也是数据库对象</span></span><br><span class="line"><span class="keyword">drop</span> <span class="keyword">trigger</span> <span class="keyword">if</span> <span class="keyword">exists</span> 触发器名称;</span><br></pre></td></tr></table></figure>

<h2 id="注意事项"><a href="#注意事项" class="headerlink" title="注意事项"></a>注意事项</h2><p>忌: 使用count( * )判断是否存在符合条件的数据</p>
<p>荐: 使用select … limit 1</p>
<p>忌: 在执行一个更新语句后, 使用查询方式判断此更新语句是否有执行成功</p>
<p>荐: 使用row_count( )函数判断修改行数</p>
<script type="text/javascript" src="https://cdn.jsdelivr.net/npm/kity@2.0.4/dist/kity.min.js"></script><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/kityminder-core@1.4.50/dist/kityminder.core.min.js"></script><script defer="true" type="text/javascript" src="https://cdn.jsdelivr.net/npm/hexo-simple-mindmap@0.2.0/dist/mindmap.min.js"></script><link rel="stylesheet" type="text/css" href="https://cdn.jsdelivr.net/npm/hexo-simple-mindmap@0.2.0/dist/mindmap.min.css">
      
    </div>

    
    
    

    <footer class="post-footer">
        <div class="post-eof"></div>
      
    </footer>
  </article>
</div>




  <nav class="pagination">
    <a class="extend prev" rel="prev" href="/"><i class="fa fa-angle-left" aria-label="上一页"></i></a><a class="page-number" href="/">1</a><span class="page-number current">2</span><a class="page-number" href="/page/3/">3</a><span class="space">&hellip;</span><a class="page-number" href="/page/6/">6</a><a class="extend next" rel="next" href="/page/3/"><i class="fa fa-angle-right" aria-label="下一页"></i></a>
  </nav>

</div>
  </main>

  <footer class="footer">
    <div class="footer-inner">


<div class="copyright">
  &copy; 
  <span itemprop="copyrightYear">2023</span>
  <span class="with-love">
    <i class="fa fa-heart"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">郭泽</span>
</div>
  <div class="powered-by">由 <a href="https://hexo.io/" rel="noopener" target="_blank">Hexo</a> & <a href="https://theme-next.js.org/" rel="noopener" target="_blank">NexT.Gemini</a> 强力驱动
  </div>

    </div>
  </footer>

  
  <script src="https://cdnjs.cloudflare.com/ajax/libs/animejs/3.2.1/anime.min.js" integrity="sha256-XL2inqUJaslATFnHdJOi9GfQ60on8Wx1C2H8DYiN1xY=" crossorigin="anonymous"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.6.0/jquery.min.js" integrity="sha256-/xUj+3OJU5yExlq6GSYGSHk7tPXikynS7ogEvDej/m4=" crossorigin="anonymous"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/fancybox/3.5.7/jquery.fancybox.min.js" integrity="sha256-yt2kYMy0w8AbtF89WXb2P1rfjcP/HTHLT7097U8Y5b8=" crossorigin="anonymous"></script>
<script src="/js/comments.js"></script><script src="/js/utils.js"></script><script src="/js/motion.js"></script><script src="/js/next-boot.js"></script><script src="/js/bookmark.js"></script>

  
<script src="https://cdnjs.cloudflare.com/ajax/libs/hexo-generator-searchdb/1.4.0/search.js" integrity="sha256-vXZMYLEqsROAXkEw93GGIvaB2ab+QW6w3+1ahD9nXXA=" crossorigin="anonymous"></script>
<script src="/js/third-party/search/local-search.js"></script>

  <script class="next-config" data-name="pdf" type="application/json">{"object_url":{"url":"https://cdnjs.cloudflare.com/ajax/libs/pdfobject/2.2.8/pdfobject.min.js","integrity":"sha256-tu9j5pBilBQrWSDePOOajCUdz6hWsid/lBNzK4KgEPM="},"url":"/lib/pdf/web/viewer.html"}</script>
  <script src="/js/third-party/tags/pdf.js"></script>

  <script class="next-config" data-name="mermaid" type="application/json">{"enable":true,"theme":{"light":"default","dark":"dark"},"js":{"url":"https://cdnjs.cloudflare.com/ajax/libs/mermaid/9.1.3/mermaid.min.js","integrity":"sha256-TIYL00Rhw/8WaoUhYTLX9SKIEFdXxg+yMWSLVUbhiLg="}}</script>
  <script src="/js/third-party/tags/mermaid.js"></script>

  <script src="/js/third-party/fancybox.js"></script>


  





</body>
</html>
